How to do search multiple values in specific column in SQL server

Question

1.00/5 (2 votes)

See more:

I have strings and numeric values in column, I want to search base on numeric values only in sql stored procedure kindly guide me.

example :
column values="3 Bleaching , hair lotions; dentifrices. 9 Glasses, eyeglass lens,
14 Precious metals and their alloys 18 Leather and imitations of leather"

so when I want to search 3 , 9 ,14 or 18 then above record should come.

What I have tried:

select * from SearchProductGoodsClass where Remarks like '%wipo%'

and GoodsDescription like '%[3,18]%'

but this shows range between 3 to 18 but i want only that record which has 3 and 18

Posted 13-Jan-22 20:52pm

Noman Suleman

Updated 13-Jan-22 22:34pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2022-01-13T21:28:00

The best approach is it change your DB design so you aren't mixing quantities with items - as it is, you have a real problem because while it's possible to do there is no good way to do it, and you will almost certainly get a lot of "false positives".

SQL

SELECT ... WHERE MyColumn LIKE '%3%' OR
                 MyColumn LIKE '%9%' OR
                 MyColumn LIKE '%14%' OR
                 MyColumn LIKE '%18%'

Or perhaps

SQL

SELECT ... WHERE MyColumn LIKE '%3%' AND
                 MyColumn LIKE '%18%'

Will do it, but that will give false positives for "23 little hats" and "218 Fluffy pillows" as well.

SQL does not have "proper" regexes, and the way you are storing your data is a problem. To get accurate results you need accurate data, and yours just isn't that.

_Asif_ · Answer 2 · 2022-01-13T22:34:00

This is true that you have a bad design, all searchable fields should be logged in search-friendly way to make search faster.

But still, there is a way to accommodate your requirement.

Below SQL Function parse (In not so optimized way) a string for a number and if it founds it returns it as an integer.

SQL

CREATE FUNCTION dbo.fn_GetNumbers(@strInput NVARCHAR(500))
RETURNS INT
AS
BEGIN
DECLARE @strOut NVARCHAR(500) = '', @intCounter INT = 1, @OutValue INT
WHILE @intCounter <= LEN(@strInput) 
BEGIN
    SELECT @strOut = @strOut + CASE WHEN SUBSTRING(@strInput, @intCounter, 1) LIKE '[0-9]' THEN SUBSTRING(@strInput, @intCounter, 1) ELSE '' END    
    SET @intCounter = @intCounter + 1 

END

IF (LEN(@strOut) > 0)
	SELECT @OutValue = cast(@strOut as int)
else
	SELECT @OutValue = null

RETURN @OutValue
END

Once you have that function you can use it in the below SQL to get the desired result.

SQL

SELECT GoodDescription, dbo.fn_GetNumbers(GoodDescription)
FROM 
(
SELECT '3 Bleaching'  as GoodDescription
UNION ALL
SELECT 'hair lotions'
UNION ALL
SELECT 'dentifrices'
UNION ALL
SELECT '9 Glasses'
UNION ALL
SELECT 'eyeglass lens'
UNION ALL
SELECT '14 Precious metals and their alloys'
UNION ALL
SELECT '18 Leather and imitations of leather'
) T
WHERE dbo.fn_GetNumbers(GoodDescription) IN (3, 18)

How to do search multiple values in specific column in SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0