Click here to Skip to main content
15,168,971 members
Please Sign up or sign in to vote.
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
Updated 4 days ago

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.
   
Comments
Noman Suleman 4 days ago
   
Thanks. agreed with you. but i can not change DB design, but i am trying to implement as you said.
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)
   
Comments
Noman Suleman 4 days ago
   
I have tested that function its return all numeric values from string like '391418' but it is impossible to get result based on that combined values.. but your second query i did not understand how do use it. because my all values in same column which you have show in query wise line in second query
CHill60 4 days ago
   
The second query is just an example of calling the function - it's test data
Noman Suleman 4 days ago
   
ok but its not calling the mentioned row.


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 3' //this line not calling if 2 numeric in a cloumn
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)

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900