We help IT Professionals succeed at work.

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

156 Views
Last Modified: 2019-01-06
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
Comment
Watch Question

SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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 BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Author

Commented:
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 BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Author

Commented:
@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/
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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 RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Thanks, Wayne..
FYI, Slightvw solution will also work, kindly try once..
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.