SQL Query - Problem between '=' and '%'

Question

0.00/5 (No votes)

See more:

Hi,

I have a following kind of records in table (just one field is shown here),

PCA-SM10              <br />
PCA-SM10H           <br />
PCA-S(PS)M6H

when I'm selecting data with,
SELECT * FROM [TABLE_NAME] WHERE [COLUMN] = @MyParameter
works fine but not for PCA-S(PS)M6H.

So I moved to the
SELECT * FROM [TABLE_NAME] WHERE [COLUMN] LIKE '%' + @MyParameter + '%'
now this works fine for PCA-S(PS)M6H, but when I'm passing PCA-SM10 as parameter, it showing mw both the records i.e PCA-SM10 and PCA-SM10H. I want only data that has exact match.

Procedure :

SQL

SELECT con.[Standard], STR(con.[Concentration(wt%)], 7, 4) AS 'Concentration(wt%)',
STR(con.[Sulfur(wt%)], 7, 4) AS 'Sulfur(wt%)', con.[g Bi/L], con.[Lead g/Gal], con.[ug/cm2],
con.[Ba(wt%)], con.[Ca(wt%)], con.[Cl(wt%)], con.[Cu(wt%)], con.[Mg(wt%)], con.[P(wt%)], con.[S(wt%)], con.[Zn(wt%)], con.[Br(wt%)], con.[Cd(wt%)],
con.[Cr(wt%)], con.[Hg(wt%)], con.[Pb(wt%)], con.[Al(wt%)], con.[F(wt%)], con.[Na(wt%)], con.[Si(wt%)], con.[Ti(wt%)], con.[Bromine Index], con.[Bromine Number],
con.[Acid Number], con.[Base Number], con.[ng/uL], con.[mg/kg],
con.PPM, con.[Iron (PPM)], con.[Nickel (PPM)], con.[Vanadium (PPM)],
 STUFF((
    SELECT  ', ' + m.Method
    FROM Method m, ProductMethod pm
    WHERE m.MethodId=pm.MethodId AND con.ProductCode=pm.ProductCode
    FOR XML PATH('')
    ),1, 2, '') as Methods
FROM ProductConcenPPM con
WHERE con.ProductCode = @productCode

How do I do that ?
------------------------------------------------------------------------------
In other words,

Lets say there are 5 products with ProductCode, XX-ABC, XX-ABCD, XX-DEF, XX-DEFG, XX-W(XY)Z
So I want only that product that has exact ProductCode, which I'm supplying.
e.g. If I'm passing XX-ABC, it should return me only XX-ABC products and not XX-ABCD

But as you can see, there is '()' in Product Code, So I can't apply '=' operator in WHERE clause. And If I'm going for 'like % %' in WHERE clause, it would cause problem, like if I'm passing XX-ABC as parameter, So the output would be generated for XX-ABC & XX-ABCD.

I want the records for XX-ABC and same for XX-W(XY)Z

How do I achieve that ?

Posted 11-Feb-14 2:07am

Krunal Rohit

Updated 11-Feb-14 3:01am

v3

Add a Solution

Comments

Vedat Ozan Oner 11-Feb-14 8:27am

it should work for '='. Have you checked your data and your parameter for any leading/trailing white space?

Krunal Rohit 11-Feb-14 8:59am

I have tried all the possible things that I could but no luck.

Vedat Ozan Oner 11-Feb-14 9:14am

do you have sql profiler? you can check what actually goes to your server as query by using this tool. according to your comments, your parameter's value changes somehow on the client side.

Krunal Rohit 11-Feb-14 9:16am

Well, this query works for all the parameters except mentioned one.

Vedat Ozan Oner 11-Feb-14 9:25am

do you have any other sample like that? could you change one of rows value to make it a similar pattern as in that sample and try it again? then see what happens? using '=' operator

Krunal Rohit 11-Feb-14 9:27am

Na, I can't do that.

Vedat Ozan Oner 11-Feb-14 9:49am

then have you tried sql profiler to see what goes to db server?

5 solutions

Solution 1

you are not explaining with enough details. I tried below snippet and it works fine without any issue...

SQL

CREATE TABLE Test
	(
     ID int NOT NULL IDENTITY (1, 1),
     details varchar(30)
    );

INSERT INTO Test
(details)
VALUES
('PCA-SM10'),
('PCA-SM10H'),
('PCA-S(PS)M6H');

And query with where clause

SQL

Declare @Value Varchar(100)
Set @Value = 'PCA-S(PS)M6H'
Select * From Test Where details = @Value

Check it with
http://sqlfiddle.com/[^] :)

Posted 11-Feb-14 2:21am

nirav raval

Comments

Krunal Rohit 11-Feb-14 8:30am

This is what I have done so far,

SELECT con.[Standard], STR(con.[Concentration(wt%)], 7, 4) AS 'Concentration(wt%)',
STR(con.[Sulfur(wt%)], 7, 4) AS 'Sulfur(wt%)', con.[g Bi/L], con.[Lead g/Gal], con.[ug/cm2],
con.[Ba(wt%)], con.[Ca(wt%)], con.[Cl(wt%)], con.[Cu(wt%)], con.[Mg(wt%)], con.[P(wt%)], con.[S(wt%)], con.[Zn(wt%)], con.[Br(wt%)], con.[Cd(wt%)],
con.[Cr(wt%)], con.[Hg(wt%)], con.[Pb(wt%)], con.[Al(wt%)], con.[F(wt%)], con.[Na(wt%)], con.[Si(wt%)], con.[Ti(wt%)], con.[Bromine Index], con.[Bromine Number],
con.[Acid Number], con.[Base Number], con.[ng/uL], con.[mg/kg],
con.PPM, con.[Iron (PPM)], con.[Nickel (PPM)], con.[Vanadium (PPM)],
STUFF((
SELECT ', ' + m.Method
FROM Method m, ProductMethod pm
WHERE m.MethodId=pm.MethodId AND con.ProductCode=pm.ProductCode
FOR XML PATH('')
),1, 2, '') as Methods
FROM ProductConcenPPM con
WHERE con.ProductCode = @productCode

Solution 2

if you want exact one

SQL

declare @name nvarchar(max)
set @name= 'PCA-SM10'
select *From test where details like @name+'%'

Posted 11-Feb-14 2:25am

King Fisher

Updated 11-Feb-14 2:31am

v3

Comments

Krunal Rohit 11-Feb-14 8:26am

It would return me both PCA-SM10 and PCA-SM10H

Krunal Rohit 11-Feb-14 8:28am

Read the question again.

King Fisher 11-Feb-14 8:33am

whats your expected Result/

Krunal Rohit 11-Feb-14 8:42am

Lets say there are 5 products with ProductCode, XX-ABC, XX-ABCD, XX-DEF, XX-DEFG, XX-W(XY)Z
So I want only that product that has exact ProductCode, which I'm supplying.
e.g. If I'm passing XX-ABC, it should return me only XX-ABC products and not XX-ABCD

But as you can see, there is '()' in Product Code, So I can't apply '=' operator in WHERE clause.

King Fisher 12-Feb-14 0:00am

select * from Test where details like 'XX-W(XY)Z'

Solution 3

Hi,

This is a strange problem because '=' should work as expected...

Try this:
1. Go back to your previous solution:

SELECT * FROM [TABLE_NAME] WHERE [COLUMN] = @MyParameter

2. Declare @MyParameter as NVARCHAR, not VARCHAR.

Let me know the result.

Posted 11-Feb-14 2:46am

Andrius Leonavicius

Comments

Krunal Rohit 11-Feb-14 8:47am

I have done that, No Luck..

Andrius Leonavicius 11-Feb-14 8:57am

If you're selecting without parameter (= 'PCA-S(PS)M6H' instead of = @MyParameter), does it always works correctly?

Krunal Rohit 11-Feb-14 9:00am

Ya, because other parameters doesn't contain that parenthesis.

Andrius Leonavicius 11-Feb-14 10:01am

So, you're dealing with FOR XML PATH... I didn't realized it at first. I would try to get values from XML as NVARCHAR(MAX) type.

If that doesn't work, I could think of a workaround.
For example:
1. Use your second solution:
SELECT * FROM [TABLE_NAME] WHERE [COLUMN] LIKE '%' + @MyParameter + '%'
2. Insert these values into table variable and then get the right one from there.

Krunal Rohit 11-Feb-14 10:03am

Ignore that 'STUFF' query. It has nothing to do with my problem.
Let me give it a try.

Andrius Leonavicius 11-Feb-14 10:26am

OK, then you should temporary eliminate 'STUFF' query while searching for the real problem...

Andrius Leonavicius 11-Feb-14 10:33am

Could you show us how you're declaring and setting @productCode?

Solution 4

Hi,

Check the below Answer.... I think It will helps you. Because the problem is XML Conversion.
you need to use FOR XML PATH with TYPE Conversion as belwo.

SQL

-- Tables Creation
IF OBJECT_ID('TempDB..#Test') IS NOT NULL DROP TABLE #Test
CREATE TABLE #Test (ID INT, Name VARCHAR(30))
-- Sample Data
INSERT INTO #Test(ID, Name) VALUES(1,'Test'),(2,'&Test'), (3,'<test>')
-- Actual Data
SELECT ID, Name FROM #Test
-- Reguired Output
'Test,&Test,<test>'
SELECT STUFF((SELECT ','+Name AS [text()] FROM #Test FOR XML PATH('')),1,1,'')
-- Current Output
'Test,&Test,<test>'
-- SQL Query
SELECT STUFF((SELECT ','+Name AS [text()] FROM #Test FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') AS 'NameList'
</test></test></test>

How to eliminate XML Escape Characters in "FOR XML PATH"?[^]

Try this....

SQL

SELECT con.[Standard], STR(con.[Concentration(wt%)], 7, 4) AS 'Concentration(wt%)',
STR(con.[Sulfur(wt%)], 7, 4) AS 'Sulfur(wt%)', con.[g Bi/L], con.[Lead g/Gal], con.[ug/cm2],
con.[Ba(wt%)], con.[Ca(wt%)], con.[Cl(wt%)], con.[Cu(wt%)], con.[Mg(wt%)], con.[P(wt%)], con.[S(wt%)], con.[Zn(wt%)], con.[Br(wt%)], con.[Cd(wt%)],
con.[Cr(wt%)], con.[Hg(wt%)], con.[Pb(wt%)], con.[Al(wt%)], con.[F(wt%)], con.[Na(wt%)], con.[Si(wt%)], con.[Ti(wt%)], con.[Bromine Index], con.[Bromine Number],
con.[Acid Number], con.[Base Number], con.[ng/uL], con.[mg/kg],
con.PPM, con.[Iron (PPM)], con.[Nickel (PPM)], con.[Vanadium (PPM)],
 STUFF((
    SELECT  ', ' + m.Method AS [text()]
    FROM Method m, ProductMethod pm
    WHERE m.MethodId=pm.MethodId AND con.ProductCode=pm.ProductCode
    FOR XML PATH(''),TYPE).VALUE('.','VARCHAR(MAX)')
    ,1, 2, '') as Methods
FROM ProductConcenPPM con
WHERE con.ProductCode = @productCode

Regards,
GVPrabu

Posted 11-Feb-14 3:33am

gvprabu

Updated 11-Feb-14 5:03am

v5

Comments

Krunal Rohit 11-Feb-14 9:35am

Read the question and all the comments carefully.

Krunal Rohit 11-Feb-14 9:51am

If I'm not mistaken, it would probably throw this kinda error :
'"VALUE" is not a valid function, property, or field.'

gvprabu 11-Feb-14 10:06am

ok then I think I given '(' is wrong. So pls check the sample Query and change yours as same.

gvprabu 11-Feb-14 11:03am

I updated the solution (Added 'AS [text()]')

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)

Krunal Rohit · Accepted Answer · 2014-02-12T04:41:00

The data was in truncated form and after changing that data my procedure worked well. :)
So make sure that whenever you are having this kind of problem, remove the data and enter it again (Ctrl C + V is strongly not recommended, because for large database it would cause burden for DBA or programmer).

And thanks for your time, efforts and answers.
Happy Coding :)

-KR

SQL Query - Problem between '=' and '%'

5 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us