Avatar of Wayne Barron
Wayne BarronFlag for United States of America 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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Raja Jegan R

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Raja Jegan R

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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;
ASKER
Wayne Barron

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
ASKER
Wayne Barron

@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/
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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..