0

For example, I want to find strings that contain AAA or BBB or ... ZZZ, I would need to use something like below:

select * from your_table
where UPPER(your_field) like '%AAA%'
or UPPER(your_field) like '%BBB%'
or UPPER(your_field) like '%CCC%'
or UPPER(your_field) like '%DDD%'
or UPPER(your_field) like '%EEE%'
or UPPER(your_field) like '%FFF%'
or UPPER(your_field) like '%GGG%'
or UPPER(your_field) like '%HHH%'
or UPPER(your_field) like '%III%'
or UPPER(your_field) like '%JJJ%'
or UPPER(your_field) like '%KKK%'
or UPPER(your_field) like '%LLL%'
or UPPER(your_field) like '%MMM%'
or UPPER(your_field) like '%NNN%'
or UPPER(your_field) like '%OOO%'
or UPPER(your_field) like '%PPP%'
or UPPER(your_field) like '%QQQ%'
or UPPER(your_field) like '%RRR%'
or UPPER(your_field) like '%SSS%'
or UPPER(your_field) like '%TTT%'
or UPPER(your_field) like '%UUU%'
or UPPER(your_field) like '%VVV%'
or UPPER(your_field) like '%WWW%'
or UPPER(your_field) like '%XXX%'
or UPPER(your_field) like '%YYY%'
or UPPER(your_field) like '%ZZZ%'

The above code seems to be tad cumbersome. Is there more efficient way to write it out?

halfer
  • 19,824
  • 17
  • 99
  • 186
Isaac A
  • 543
  • 1
  • 6
  • 18
  • You don't need `UPPER` unless the column collation is case-sensitive. – Dan Guzman Jan 23 '23 at 21:26
  • @DanGuzman Ignoring the UPPER, is there a better way to write the code? – Isaac A Jan 23 '23 at 21:26
  • the only other option I could think of is creating a CLR function in C#. It would take a regex and the column as parameters and you would just use C# regex functions to check if the character appears 3 times in a row. Not sure if that is viable which is why im posting this as a comment. https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-user-defined-functions?view=sql-server-ver16. I've done this in my past job and it worked pretty well. – markfila Jan 23 '23 at 21:38
  • TSQL doesn't support regex and back references without CLR. You could do a "fun" task of splitting into characters and looking for three consecutive ones the same in a variety of ways but this would not be more efficient in terms of execution time or development time – Martin Smith Jan 23 '23 at 21:42
  • 2
    if you don't want to go to dark woods of bad performance/maintenance, i think what you wrote is hard to beat. You could encaplusate the code into a inline table value function if you want to reuse it for many columns – siggemannen Jan 23 '23 at 21:43
  • 2
    You could create a table for your patterns (AAA, BBB, etc) and then use a cartesian join to it such as this... `select m.* from my_data m, patterns p where m.col1 like concat('%', p.pattern_col, '%')` – Isolated Jan 23 '23 at 21:47
  • 1
    Or generate that on the fly with `SELECT '%' + REPLICATE(CHAR(value),3) + '%' FROM GENERATE_SERIES(ASCII('A'), ASCII('Z'))` https://dbfiddle.uk/V5I9V2Q- – Martin Smith Jan 23 '23 at 21:51
  • No native regex support in SQL Server, sad to say. Other RDBMS makes and versions do have that support. – O. Jones Jun 19 '23 at 11:12

0 Answers0