2

I need to implement pattern matching using sqlserver...

These are the following conditions-

  1. 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

user1050619
  • 19,822
  • 85
  • 237
  • 413

1 Answers1

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 (^).

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • How does the '*' will be handled?? – user1050619 May 06 '13 at 02:00
  • Can you explain more on ^ character..In python ^ means "matches the beginning line of string for ex:^He will be True for "Hello" " – user1050619 May 06 '13 at 17:08
  • `^` 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. – RichardTheKiwi May 06 '13 at 22:51