Search string with regular expression SQL Server

Question

The regex I want to use is: ^(?=.*[,])(,?)ABC(,?)$

What I want to get out is:

^                // start
(?=.*[,])        // contains at least one comma (,)
(,?)ABC(,?)      // The comma is either in the beginning or in the end of the string "ABC"
$                // end

Of course ABC is ought to be a variable based on my search term.

So if ABC = 'abc' then ",abc", "abc,", ",abc," will match but not "abc" or "abcd"

Better way to do this is also welcome.

The value in the record looks like "abc,def,ghi,ab,cde..." and I need to find out if it contains my element (i.e. 'abc'). I cannot change the data structure. We can assume that in no case the record will contain only one sub-value, so it is correct to assume that there always is a comma in the value.

So you wish to search for a term in a string having comma separated terms ? — user2705585
– user2705585, Commented Feb 4, 2016 at 1:54
Yes basically the regex tells it all. It must have one comma either in the beginning or end to make sure my string is not substring of other string, and it will match if it's first string or last as well. — DDan
– DDan, Commented Feb 4, 2016 at 1:58
how about splitting the string with comma delimiter then search after splitting? — Angelo
– Angelo, Commented Feb 4, 2016 at 2:01
What you currently have is correct. Just don't use ^ and $. Use only (,?)(ABC)(,?) and capture the second group by using $2 or \2 whatever is compatible with sql-server. Or by using non capturing groups like this. — user2705585
– user2705585, Commented Feb 4, 2016 at 2:04

Gordon Linoff · Accepted Answer · 2016-02-04 01:53:02Z

2

If you want to know if a comma delimited string contains abc, then I think like is the easiest method in any database:

where ',' + col + ',' like '%,abc,%'

answered Feb 4, 2016 at 1:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DDan Over a year ago

That will not match "abc,def"

DDan Over a year ago

"abc" could be the first or last item

Gordon Linoff Over a year ago

@Ddan . . . Of course this will. That is why commas are pre- and post-pended before the like.

Collectives™ on Stack Overflow

Search string with regular expression SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related