Reputation: 549
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
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
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