Pattern match any 3 repeating letters

• sqldriver

  SSChampion

  Points: 10440

  More actions

  November 7, 2013 at 11:31 am

  #395790

  Is there a more concise way than something like this?

  or name like 'aaa%'

  or name like 'bbb%'

  or name like 'ccc%'

  or name like 'ddd%'

  or name like 'eee%'

  or name like 'fff%'

  or name like 'ggg%'

  or name like 'hhh%'

  or name like 'iii%'

  or name like 'jjj%'

  or name like 'kkk%'

  or name like 'lll%'

  or name like 'mmm%'

  or name like 'nnn%'

  or name like 'ooo%'

  or name like 'ppp%'

  or name like 'qqq%'

  or name like 'rrr%'

  or name like 'sss%'

  or name like 'ttt%'

  or name like 'uuu%'

  or name like 'vvv%'

  or name like 'www%'

  or name like 'xxx%'

  or name like 'yyy%'

  or name like 'zzz%'

  Thanks

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 7, 2013 at 11:45 am

  #1664882

  Something like this?

  name LIKE '[a-z][a-z][a-z]%'

  Nevermind, I missed something.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• sqldriver

  SSChampion

  Points: 10440

  More actions

  November 7, 2013 at 12:02 pm

  #1664889

  Yeah, I tried the same thing at first. This seems to work:

  case when substring(name, 1, 3) = replicate(substring(name, 1, 1), 3)

  Though I'm sure there's something that performs better. 😉

• mickyT

  SSChampion

  Points: 10360

  More actions

  November 7, 2013 at 12:07 pm

  #1664891

  I'm sure there is a better way to do this, but you could try this

  with testvalues as ( --some sample test data

  select *

  from (values (1,'blah blah'),(2,'bllaaahh'),(3,'blahhhh'),(4,'bbbblah')) s (id, name)

  ),

  cteTally as ( -- inline tally, 100 rows

  select row_number() OVER (ORDER BY (SELECT NULL)) N

  from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e1 (N),

  (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e2 (N)

  ),

  splitname as ( -- split name into single characters

  select id, N, substring(name, N, 1) C

  from testvalues t

  cross apply (select top (len(name)) N from cteTally) c

  )

  select t.id, t.name

  from testvalues t

  inner join (

  select distinct case when lag(C,1) over (order by id, N) = C and lag(C,2) over (order by id, N) = C then ID end ID

  from splitname

  ) s ON t.ID = s.ID

• mickyT

  SSChampion

  Points: 10360

  More actions

  November 7, 2013 at 12:13 pm

  #1664893

  Seeing your previous post, you could change it slightly to

  select *

  from (values (1,'blah blah'),(2,'bllaaahh'),(3,'blahhhh'),(4,'bbbblah')) s (id, name)

  where name like replicate(substring(name,1,1),3) + '%'

  That might perform better.

• sqldriver

  SSChampion

  Points: 10440

  More actions

  November 7, 2013 at 12:21 pm

  #1664894

  mickyT (11/7/2013)

  ---

  Seeing your previous post, you could change it slightly to

  select *

  from (values (1,'blah blah'),(2,'bllaaahh'),(3,'blahhhh'),(4,'bbbblah')) s (id, name)

  where name like replicate(substring(name,1,1),3) + '%'

  That might perform better.

  That is snappy. I didn't think that the + '%' would work outside of a dynamic block.

  Thanks

• mickyT

  SSChampion

  Points: 10360

  More actions

  November 7, 2013 at 12:30 pm

  #1664897

  I'm annoyed that I didn't read the question properly first up and over complicated it in my first reply:-)

• sqldriver

  SSChampion

  Points: 10440

  More actions

  November 7, 2013 at 12:38 pm

  #1664898

  I suppose I can let it slide this time, since I posted the question before thinking about about it for a minute. I was not thinking outside the brackets, and it seemed daunting.

• Louis Hillebrand

  SSCarpal Tunnel

  Points: 4982

  More actions

  November 7, 2013 at 1:01 pm

  #1664905

  You can use this query with the testvalues.

  The WHERE filter on 3 * the same character to start (REPLICATE(LEFT(t.name,1),3) + '%')

  and secondly on starting with a character between a and z

  SELECT t.id, t.name

  FROM testvalues t

  WHEREt.name LIKE REPLICATE(LEFT(t.name,1),3) + '%'

  ANDt.name LIKE '[a-z]%'

• sqldriver

  SSChampion

  Points: 10440

  More actions

  November 7, 2013 at 1:45 pm

  #1664914

  Louis Hillebrand (11/7/2013)

  ---

  You can use this query with the testvalues.

  The WHERE filter on 3 * the same character to start (REPLICATE(LEFT(t.name,1),3) + '%')

  and secondly on starting with a character between a and z

  SELECT t.id, t.name

  FROM testvalues t

  WHEREt.name LIKE REPLICATE(LEFT(t.name,1),3) + '%'

  ANDt.name LIKE '[a-z]%'

  Good point, though I should have said letters or numbers in my topic title.

  Thanks

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  November 7, 2013 at 2:24 pm

  #1664926

  erikd (11/7/2013)

  ---

  Louis Hillebrand (11/7/2013)

  ---

  You can use this query with the testvalues.

  The WHERE filter on 3 * the same character to start (REPLICATE(LEFT(t.name,1),3) + '%')

  and secondly on starting with a character between a and z

  SELECT t.id, t.name

  FROM testvalues t

  WHEREt.name LIKE REPLICATE(LEFT(t.name,1),3) + '%'

  ANDt.name LIKE '[a-z]%'

  Good point, though I should have said letters or numbers in my topic title.

  Thanks

  Easy enough. Just change the last line to the following to capture alphanumeric:

  like '[a-z0-9]%'

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• sqldriver

  SSChampion

  Points: 10440

  More actions

  November 7, 2013 at 2:33 pm

  #1664930

  Sean Lange (11/7/2013)

  ---

  erikd (11/7/2013)

  ---

  Louis Hillebrand (11/7/2013)

  ---

  You can use this query with the testvalues.

  The WHERE filter on 3 * the same character to start (REPLICATE(LEFT(t.name,1),3) + '%')

  and secondly on starting with a character between a and z

  SELECT t.id, t.name

  FROM testvalues t

  WHEREt.name LIKE REPLICATE(LEFT(t.name,1),3) + '%'

  ANDt.name LIKE '[a-z]%'

  Good point, though I should have said letters or numbers in my topic title.

  Thanks

  Easy enough. Just change the last line to the following to capture alphanumeric:

  like '[a-z0-9]%'

  I think I'm just going to leave it open, as any three repeating characters at the beginning of a string here are likely to be part of invalid data. Now I will hope that no one named Aaron misspells their name Aaaron. :crazy:

• mickyT

  SSChampion

  Points: 10360

  More actions

  November 7, 2013 at 2:53 pm

  #1664937

  Sorry I accidentally replied, was going to mention to watch out for underscores as well