Select letters where they are not preceeded or followed by letters e.g. FIND Ag or *Ag but not SAG or MAGNET

Question

I need to find the characters Ag in a column comprised of a variety of string lengths/string positions but where Ag does not form part of a word (e.g. SAG, i.e. Ag is not preceded and/or followed by any letters [A-Z]).

I have tried

WHERE AttributeColumn LIKE '%[^A-Z]AG[^A-Z]%' 

but it excludes results when Ag has no other character before or after (i.e. string only contains Ag). Perhaps a REGEXP with the * function? So that Ag can be preceded and/or followed by zero or more of [A-Z]...

If this could work can you provide me with an example of the query for MS SQL?

Answer 1

What about about padding the searched string with valid separators e.g. spaces? e.g.

WHERE ' ' + AttributeColumn + ' ' LIKE '%[^A-Z]AG[^A-Z]%' 

Answer 2

how about this?,

WHERE AttributeColumn LIKE '%[^A-Z]AG[^A-Z]%' 
    OR AttributeColumn LIKE 'AG[^A-Z]%' 
    OR AttributeColumn LIKE '%[^A-Z]AG' 
    OR AttributeColumn = 'AG' 

Answer 3

Could you just match the specific case of the search? If 'Ag' won't turn up in your results of course

USE tempdb;
GO
CREATE TABLE dbo.foo(bar VARCHAR(32) COLLATE Latin1_General_CS_AS);
GO
INSERT dbo.foo VALUES('John'),('john');
GO
SELECT bar FROM dbo.foo 
WHERE bar LIKE 'j%';
-- 1 row

SELECT bar FROM dbo.foo 
WHERE bar COLLATE Latin1_General_CI_AS LIKE 'j%';
-- 2 rows

GO    
DROP TABLE dbo.foo;

More info here Is the LIKE operator case-sensitive with MS SQL server?

Answer 4

Will this work for you? ([^A-Z]|\s)AG([^A-Z]|\s)