How to check a string is in a specific format in SQL?

Question

Need to find solution in SQL to check wether a string is in format

aCODE_1111111111

a - lower case letter
CODE - Uppercase letter.
Format start with aCODE_ and followed by 10 numbers. aCODE_ should be constant.

IF the input is in aCODE_1234567890 , result is true
IF the input is in aCODE_2256784558 , result is true
IF the input is in ACODE_1234567890 , result is false
IF the input is in aCODe_1234567890 , result is false
IF the input is in aCODE_123456789 , result is false

What if you have 11 digits instead of 10? should that result with `true` or `false`? — Zohar Peled, Aug 26 '19 at 10:17
Can that format be a part of the string? I mean, with `This is aCODE_0123456789 and it should be fine` - the result should be `true` or `false`? — Zohar Peled, Aug 26 '19 at 10:19
@ZoharPeled No it cannot be a part of string. This is aCODE_0123456789 and it should be fine , will result as False — Vishnu Dev, Aug 26 '19 at 10:22

Amira Bedhiafi · Accepted Answer · 2019-08-26T11:25:41.197

1

Depending on your request, your query should be like this :

SELECT CASE
            WHEN t.solution SQL_Latin1_General_CP1_CS_AS 
                like 'aCODE[_][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
                   THEN 1
                   ELSE 0
           END as solution, *
    FROM yourtable

edited Aug 26 '19 at 11:25

answered Aug 26 '19 at 10:24

Amira Bedhiafi

8,088
6
24
60

1

This might not be a case sensitive comparison. Use `collate` to make sure it is. – Zohar Peled Aug 26 '19 at 10:27
@VishnuDev you should use collate SQL_Latin1_General_CP1_CS_AS to make it case sensitive. – nunoq Aug 26 '19 at 10:33
But if I am passing this string as a parameter , then collate cannot be used. – Vishnu Dev Aug 26 '19 at 10:36
@VishnuDev you can declare it from the begining, check here : https://stackoverflow.com/questions/34096720/collate-declared-sql-variable – Amira Bedhiafi Aug 26 '19 at 10:43
underscore must be escaped – SMor Aug 26 '19 at 11:01
`aCODE_1234567890` will fail this test (even though it should pass) because it's last digit is `0`. Also, `SQL_Latin1_General_CP1_CI_AS` is the wrong collation as it's case insensitive. it should be `SQL_Latin1_General_CP1_CS_AS` The correct condition is `Col collate SQL_Latin1_General_CP1_CS_AS like 'aCODE[_][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'` – Zohar Peled Aug 26 '19 at 11:13
1

@AmiraBedhiafi your answer is correct just change last expression by `[0-9]`. Votes is not important. correct answer will be upper side always :) – Hardik Leuwa Aug 26 '19 at 11:21

How to check a string is in a specific format in SQL?

1 Answers1