asked on

SQL Server Find Number between slashes and return only records that match

OK
SqL Server 2016

This is the scenario.

Table1 has the following.

TabID, Title, GID
The GID Column has the following values. (They differ tremendously through the table)

GID Values

1/2/3/
1/3/5/
3/5/4/9/
33/7/3/11/
1/5/9/23/
8/4/5/2/33/11/
etc...

OK, I need to do a search for all the records of a given number.
So, if I search for
GID = 3
It should only give me back all records that match "3" and NOT "33"
If I search for
GID = 1
It should only give me back all records that match "1" and NOT "11"
And the same goes for search for
GID = 11
It should only give me back all records that match "11" and NOT "111"

Thanks.
Wayne

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

I'm thinking a simple charindex search would work. Just add '/' to the start and end of the search string. So, when you look for 33, you actually look for /33/

I pre-pend a leading '/' to the gid so the search works.

select * from table1 where charindex('/33/','/'+gid) > 0;

Wayne Barron

ASKER

Hey, Raja.
It took a moment when I got the code from you before I realized how to use it.
I am sorry I have not responded before now.
Worked like a complete absolute charm.

Thanks, Wayne

Wayne Barron

ASKER

@slightwv
The only issue with that is the there is NO forward slash at the start, only between and at the end.
So, if you look for
/33/
And you have this, you would not get
33/7/3/11/

slightwv (䄆 Netminder)

>>The only issue with that is the there is NO forward slash at the start

Which is why I forced one.

select * from table1 where charindex('/33/','/'+gid) > 0;

>>And you have this, you would not get

Yes it would. Try it.

Raja Jegan R

Thanks, Wayne..
FYI, Slightvw solution will also work, kindly try once..