• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 92
  • Last Modified:

wildcharacter search in a where clause

Hi tere,
what's the best practices to include this where clause in my stored procedure
and SUBSTRING(R.glrf_full_acct,15, 3)not like '81.[0-9][0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9].207%,'

Open in new window

I'm searching specifically for numbers that starts with 81 and follow by any number in any of those position but after the 15 character has a 207
1 Solution
Scott PletcherSenior DBACommented:
For SUBSTRING, the second value is the starting position and the third is the length.

Did you mean to do start in byte 3 and go at least 17 bytes, like this?:

and SUBSTRING(R.glrf_full_acct, 3, 18) not like '81.[0-9][0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9].207%,'

If the value does start in byte 15, you need to change length to 17+:

and SUBSTRING(R.glrf_full_acct, 15, 18) not like '81.[0-9][0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9].207%,'
COHFLAuthor Commented:
That is true but I need to make sure the first two are 81. I do happen to have 207 with a different starting set of numbers
Vitor MontalvãoMSSQL Senior EngineerCommented:
How about this one?
WHERE ISNUMERIC(R.glrf_full_acct)=1 AND LEFT(R.glrf_full_acct,2)='81' AND SUBSTRING(R.glrf_full_acct,15, 3) = '207'

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now