T-SQL Check string pattern

Question

Just curious that is there any easy way to filter certain string out instead of using the following method:

example: for AccountNumber attribute, that should allow exactly 10 digits as the value, like, 0123456789,

So for the query I made like :

@input like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

I am just wondering is there any alternate way to write this query? for those value which require exact 100 digits, nobody want to count while keep pasting [0-9], right? I notice there is something in C# like ^(\d{10})$, but I cannot find such matching method in TSQL, does this similar method exist?

Gordon Linoff · Accepted Answer · 2016-09-29 21:14:00Z

6

Your logic is fine. You can also write this as:

where len(AccountNumber) = 10 and AccountNumber not like '%[^0-9]%'

That is, the length is 10 and it contains no characters that are not digits.

answered Sep 29, 2016 at 21:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Community · Accepted Answer · 2017-04-13 12:42:39Z

2

You could use

@input like REPLICATE('[0-9]',10) COLLATE Latin1_General_100_BIN2

The explicit collate clause is because in some collations the range will match things that aren't strictly digits.

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Sep 29, 2016 at 21:15

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Collectives™ on Stack Overflow

T-SQL Check string pattern

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related