CODEWITHSUNDEEP
sqlsql-serverspecial-characterssql-like
Naresh
Naresh

Reputation: 657

How to use special characters in SQL Server LIKE clause

I have a table in which I want to get the strings which are like ab aabb aaabbb ...... a n times followed by b n times as shown below.

Eg TABLE:

  value 
---------- 
   ab
   aabb
   aaabbb
   aaaabbbb
   1
   1a
   abababa

I want the result TABLE to be:

 value    
---------- 
ab
aabb
aaabbb
aaaabbbb

I've tried like this

select * from [NumTest] where value LIKE '[a]+[b]+'

but it's returning zero rows.

Can anybody help me how to use special characters in SQL Server's LIKE ?

Upvotes: 1

Views: 370

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Here is something that can work:

(EDIT - after O/P comment, commented parts not needed)

--WITH CTE_GoodValues AS 
--(
    SELECT value
    FROM Table1
    WHERE LEFT(VALUE,LEN(VALUE)/2) = REPLICATE('a',LEN(VALUE)/2)
        AND RIGHT(VALUE,LEN(VALUE)/2) = REPLICATE('b',LEN(VALUE)/2)
        AND LEN(VALUE)%2=0
--)
--SELECT REPLICATE(' ', (SELECT MAX(LEN(VALUE))/2 FROM CTE_GoodValues)- LEN(VALUE)/2) + VALUE
--FROM CTE_GoodValues

In the CTE - select values that have left half all a-s and right half all b-s. Then find MAX length and use it to replicate needed empty spaces in front

DEMO (after edit)

Upvotes: 2

Related Questions