CODEWITHSUNDEEP
sql-serverstringt-sql
PNPTestovir
PNPTestovir

Reputation: 307

Find strings in non alphabetic strings

I have to find all occurence of alphabetic strings in table. To do it, I use below algorithm (I iterate through another table in loop to get this @TableName value):

UPPER(rows_value) like '%' + @TableName + '%')

This condition is wrong because it's also shows me string contained in another string.

Let's assume that @TableName = test. I would like to find records in table, which contains this string (also surrended by non alphabetical characters). My algorithm returns me rows contained:

test
(test)
test0x
test02
_test_2222
pretest <---
uptest <----
...

I don't need last two, because these are different words. How to modify my condition to exclude non needed results?

Upvotes: 2

Views: 45

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Try next query:

DECLARE @TableName VARCHAR(128) = 'test' -- Replace with propper data type and max length
SELECT  *
FROM (VALUES 
    ('test'),
    ('(test)'),
    ('test0x'),
    ('test02'),
    ('_test_2222'),
    ('pretest '),
    ('uptest'),
    ('testAlpha'),
    ('13223 tes3432')
) t(Col1)
WHERE   t.Col1 LIKE '%' + @TableName + '%'
AND     NOT(t.Col1 LIKE '%[a-z]' + @TableName + '%' OR t.Col1 LIKE '%' + @TableName + '[a-z]%')

Upvotes: 2

Related Questions