sqlserver pattern matching on a particular variable

Question

I need to implement pattern matching using sqlserver...

These are the following conditions-

The first letter of the word should start with a character. 2.It can be upper or lower case 3.The following characters after the 1st chacter can by numbers 1-9 or any valid characters or underscore..

Basically I need to implement this regular expression logic in sqlserver

'/^[a-zA-Z][a-zA-Z0-9_]*$/

I'm sure this will work

ie;

select var_1 from table_1 where var_1 like [a-z] but not sure how if all the logic can be implemented

possible duplicate of SQL Server Regular expressions in T-SQL — flup
– flup, Commented May 6, 2013 at 1:29
Check this out for SQL-Server 2005+: msdn.microsoft.com/en-us/magazine/cc163473.aspx — Pieter Geerkens
– Pieter Geerkens, Commented May 6, 2013 at 1:29

RichardTheKiwi · Accepted Answer · 2013-05-06 02:11:23Z

4

For the RegEx /^[a-zA-Z][a-zA-Z0-9_].*$/:

WHERE somecolumn LIKE '[a-Z][a-Z0-9_]%'  -- column
WHERE @somecolumn LIKE '[a-Z][a-Z0-9_]%' -- variable

i.e.

select var_1 from table_1 where var_1 like '[a-Z][a-Z0-9_]%'

FYI Unless you have changed the defaults, most SQL Server databases are not collated to be case sensitive. Therefore, the LIKE range [a-z] will include all letters, upper or lower case. The singular character % matches ZERO to ANY number of characters.

The start of the string (^ in RegExp) is implicit by the fact that the LIKE pattern is not prefixed by %.

Reference: LIKE (Transact-SQL)

For the RegEx /^[a-zA-Z][a-zA-Z0-9_]*$/:

Use the pattern in T I's comment, i.e.

WHERE var_1 LIKE '[a-Z]%' AND NOT var_1 LIKE '%[^a-Z0-9_]%'

The first part ensures that the first character is a letter; the second part ensures that in any position (including first), the only valid characters are alphanumerics or underscore, by exclusion (^).

edited May 6, 2013 at 2:11

answered May 6, 2013 at 1:33

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1050619 Over a year ago

How does the '*' will be handled??

user1050619 Over a year ago

Can you explain more on ^ character..In python ^ means "matches the beginning line of string for ex:^He will be True for "Hello" "

RichardTheKiwi Over a year ago

^ is required in regular RegEx because the matches are implicitly "anywhere" so ^ anchors the start of the match to the beginning of string. In SQL Server, matches start at the beginning of string unless there is a '%' to make it match anywhere.

Jeremy Caney · Accepted Answer · 2023-10-24 05:04:12Z

0

select * ,UPPER(LEFT(DesEng, 1)) +LOWER(RIGHT(DesEng, LEN(DesEng) - 1)) as b from C_Communes where LEN(DesEng) >1

edited Oct 24, 2023 at 5:04

Jeremy Caney

7,808115 gold badges58 silver badges86 bronze badges

answered Oct 23, 2023 at 3:44

GAMES HD OFFICIAL

1

1 Comment

Jeremy Caney Over a year ago

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?

Collectives™ on Stack Overflow

sqlserver pattern matching on a particular variable

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related