1

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 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Naresh
  • 657
  • 3
  • 16
  • 35
  • What you are asking is written as `LIKE '%ab%'` but that is not going to do you much good as it will return `ababa` for example. If you want exact output you have shown much different logic is needed. Check my answer – Nenad Zivkovic Jul 11 '13 at 13:00

1 Answers1

2

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)

Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55
  • This is getting the result alright but it looks very complex to me. And I don't need any leading blanks it's just bad formatting of my question – Naresh Jul 11 '13 at 13:10
  • @ILLUMINATI7590 AH, in that case, you can just use the inner SELECT from CTE – Nenad Zivkovic Jul 11 '13 at 13:38