How to check only the numeric values at the same time ignoring the symbols or characters using SQL queries

Question

Post reply

How to check only the numeric values at the same time ignoring the symbols or characters using SQL queries

mvijaykumar.mca

SSC Journeyman

Points: 97
More actions
November 11, 2011 at 9:56 am

#390842

I have a table with a field with values given below. the ID field is a varchar type
ID
---
2|4|6
These 2,4 and 6 are actually a primary key values of a Master table.
i want the sql query to find whether the field contains the value 2.
One thing is of sure that we cannot use LIKE or IN operator as it will not provide correct solutions.
How to check only the numeric values at the same time ignoring the | symbol using SQL queries??
Please help me.

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

ID
---
2|4|6

It's bad design. If you have control on database schema, re-design it.

matzer SSCrazy Eights Points: 9727 More actions · Answer 2

Agreed with Dev, but you can do it with a LIKE:

SELECT

*

FROM

(

SELECT '2|4|6'

) SampleTable(ID)

WHERE

'|' + ID + '|' LIKE '%|2|%'

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

mvijaykumar.mca (11/11/2011)
I have a table with a field with values given below. the ID field is a varchar type
ID
---
2|4|6
These 2,4 and 6 are actually a primary key values of a Master table.
i want the sql query to find whether the field contains the value 2.
One thing is of sure that we cannot use LIKE or IN operator as it will not provide correct solutions.
How to check only the numeric values at the same time ignoring the | symbol using SQL queries??
Please help me.

Hello and welcome to SSC!

It appears that you have forgotten to post a few details, so I'm unsure what your actual question is!

What we could do with from you are four things: A clear description of your problem, DDL scripts, sample data in a readily consumable format and expected results.

When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.

For now, here's a best guess at your solution.

BEGIN TRAN

DECLARE @TABLE AS TABLE (ID VARCHAR(5))

INSERT INTO @TABLE

SELECT '2|3|5'

UNION ALL SELECT '1|2'

UNION ALL SELECT '1|3|5'

--This

SELECT ID

FROM @TABLE

WHERE ID LIKE '%2%'

--Or this

SELECT ID

FROM @TABLE

WHERE CHARINDEX('2',ID) > 0

ROLLBACK

You could also try a splitter, like the one in this article[/url]. That would allow you to check the split values for the value you are searching for.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

Peter Brinkhaus (11/11/2011)
Agreed with Dev, but you can do it with a LIKE:
SELECT
*
FROM
(
SELECT '2|4|6' -- '3|24|4'
) SampleTable(ID)
WHERE
'|' + ID + '|' LIKE '%|2|%'

Can we try the same code with ID ''3|24|4''

Lowell SSC Guru Points: 323518 More actions · Answer 5

a lot of the work around examples so far would return false positives if the values are two or more digits, thought right? 2|5|21|23|55 for example

redesign, or split the data on the | character

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

matzer SSCrazy Eights Points: 9727 More actions · Answer 6

Dev (11/11/2011)
Peter Brinkhaus (11/11/2011)
Agreed with Dev, but you can do it with a LIKE:
SELECT
*
FROM
(
SELECT '2|4|6' -- '3|24|4'
) SampleTable(ID)
WHERE
'|' + ID + '|' LIKE '%|2|%'
Can we try the same code with ID ''3|24|4''

I'm not sure what you mean. In case of ID='3|24|4' nothing is returned, which is the requirement, I guess.

matzer SSCrazy Eights Points: 9727 More actions · Answer 7

Lowell (11/11/2011)
a lot of the work around examples so far would return false positives if the values are two or more digits, thought right? 2|5|21|23|55 for example
redesign, or split the data on the | character

No need for a string splitter. Also Cadavre's solution can be simply modified to leave out the false positives:

SELECT

ID

FROM

(

SELECT '2|3|5'

UNION ALL SELECT '1|22'

UNION ALL SELECT '1|3|5'

) SampleTable(ID)

WHERE

CHARINDEX('|2|', '|' + ID + '|') > 0

mvijaykumar.mca SSC Journeyman Points: 97 More actions · Answer 8

Thanks Peter..

SELECT *

from Metatags WHERE

'|' + MetatagDesc + '|' LIKE '%|2|%'

If the field contains 2|23|4 , and am searching 23, its shud search only 23 not 2, dats the scenario i was looking for and It worked....

Thanks for everyone for the quick response... 🙂

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 9

mvijaykumar.mca (11/11/2011)
Thanks Peter..
SELECT *
from Metatags WHERE
'|' + MetatagDesc + '|' LIKE '%|2|%'
If the field contains 2|23|4 , and am searching 23, its shud search only 23 not 2, dats the scenario i was looking for and It worked....
Thanks for everyone for the quick response... 🙂

I insist you not to go with this solution. If it would have any column but key, there is no issue. You are playing with IDs. In future, there would be many issues just because of one wrong decision (or quick fix).

drew.allen SSC Guru Points: 76989 More actions · Answer 10

Peter Brinkhaus (11/11/2011)
No need for a string splitter. Also Cadavre's solution can be simply modified to leave out the false positives:

Your solution is not SARGable. If you care at all about performance, there most certainly is a need to at least consider a string splitter.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

matzer SSCrazy Eights Points: 9727 More actions · Answer 11

drew.allen (11/11/2011)
Peter Brinkhaus (11/11/2011)
No need for a string splitter. Also Cadavre's solution can be simply modified to leave out the false positives:
Your solution is not SARGable. If you care at all about performance, there most certainly is a need to at least consider a string splitter.
Drew

I realized that and I was actually responding to the statement that a LIKE wouldn't work. But I'm wondering if a string splitter would do any good in terms of performance in this case. Sure, you can split the string into a result set with a row for each ID, but there's no index on it. So how will that be SARGable.

drew.allen SSC Guru Points: 76989 More actions · Answer 12

Peter Brinkhaus (11/11/2011)
drew.allen (11/11/2011)
Peter Brinkhaus (11/11/2011)
No need for a string splitter. Also Cadavre's solution can be simply modified to leave out the false positives:
Your solution is not SARGable. If you care at all about performance, there most certainly is a need to at least consider a string splitter.
Drew
I realized that and I was actually responding to the statement that a LIKE wouldn't work. But I'm wondering if a string splitter would do any good in terms of performance in this case. Sure, you can split the string into a result set with a row for each ID, but there's no index on it. So how will that be SARGable.

It depends on the source of the string you're splitting. If you're splitting a parameter, then there is only one string to split, which will be very fast, and then you can potentially use an index seek on the table with the parsed values. If the source of the string is a field on a table, then the function will need to be called for each field which will affect the performance.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

matzer SSCrazy Eights Points: 9727 More actions · Answer 13

drew.allen (11/11/2011)
Peter Brinkhaus (11/11/2011)
drew.allen (11/11/2011)
Peter Brinkhaus (11/11/2011)
No need for a string splitter. Also Cadavre's solution can be simply modified to leave out the false positives:
Your solution is not SARGable. If you care at all about performance, there most certainly is a need to at least consider a string splitter.
Drew
I realized that and I was actually responding to the statement that a LIKE wouldn't work. But I'm wondering if a string splitter would do any good in terms of performance in this case. Sure, you can split the string into a result set with a row for each ID, but there's no index on it. So how will that be SARGable.
It depends on the source of the string you're splitting. If you're splitting a parameter, then there is only one string to split, which will be very fast, and then you can potentially use an index seek on the table with the parsed values. If the source of the string is a field on a table, then the function will need to be called for each field which will affect the performance.
Drew

Based on the OP's response it looks like the latter

SELECT *

from Metatags WHERE

'|' + MetatagDesc + '|' LIKE '%|2|%'

drew.allen SSC Guru Points: 76989 More actions · Answer 14

Even so, the performance may still be better using the string splitter than without. The only way to now for sure is thorough testing, which is why I initially said that a string splitter was at least worth considering.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA