CODEWITHSUNDEEP
regext-sqlsql-server-2012wildcard
Pugal
Pugal

Reputation: 549

Wildcard expression in SQL Server

I know that, the following query returns the rows, which are contain the exact 5 characters between the A and G 

select * 
from 
     (select 'prefixABBBBBGsuffix' code /*this will be returned. */
      union 
      select 'prefixABBBBGsuffix') rex 
where 
     code like '%A_____G%'

But I want 17 character between A and G, then like condition must have 17 underscores. So I search little in google I found [] will be used in like. Then I tried so for.

select * 
from 
    (select 'AprefixABBBBBGsuffixG' code 
     union 
     select 'AprefixABBBBGsuffixG') rex 
where 
    code like '%A[_]^17G%' /*As per my understanding, '[]' makes a set. And  
                               '^17' would be power of the set (like Mathematics).*/

Then it returns the NULL set. How can I search rows which has certain number of character in the set []?

Note:

I'm using SQL Server 2012.

Upvotes: 0

Views: 208

Answers (2)

JBJ
JBJ

Reputation: 403

same answer as previously but corrected. 17 wasn't the number, it was 18 and 19 for strings, also put in the len(textbetweenA and G) to show.

select rex.* 
from (
    select len('prefixABBBBBGsuffix') leng, 'AprefixABBBBBGsuffixG' code 
    union 
    select len('prefixABBBBGsuffix'), 'AprefixABBBBGsuffixG'
    union
    select 0, 'A___________________G'
    ) rex 
where 
    rex.code like '%A' + replicate('_',19) + 'G%'

--and with [] the set would be [A-Za-z]. Notice this set does not match the A___________________G string.

select rex.* 
from (
    select len('prefixABBBBBGsuffix') leng, 'AprefixABBBBBGsuffixG' code 
    union 
    select len('prefixABBBBGsuffix'), 'AprefixABBBBGsuffixG'
    union
    select 0, 'A___________________G'
    ) rex 
where 
    rex.code like '%A' + replicate('[A-Za-z]',19) + 'G%'

[A-Za-z0-9] matches one character within the scope of alphabet (both cases) or a number 0 through 9

I can't find any working information about another way to handle a number of chars like that, replicate is just a way to ease parameterization and typing.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176324

I would use REPLICATE to generate desired number of '_':

select * from (
  select 'prefixABBBBBGsuffix' code 
  union 
  select 'prefixABBBBGsuffix'  
) rex 
where code like '%A' + REPLICATE('_',17) + 'G%';

Upvotes: 2

Related Questions