SQL查询 - '='和'％'之间的问题 [英] SQL Query - Problem between '=' and '%'
问题描述
我在表格中有以下类型的记录（这里只显示一个字段），
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
当我选择数据时，
SELECT * FROM [TABLE_NAME] WHERE [COLUMN] = @MyParameter
工作正常但 PCA-S（PS）M6H 。
所以我搬到了
SELECT * FROM [TABLE_NAME] WHERE [COLUMN] LIKE'％'+ @MyParameter +'％'
现在
这适用于 PCA-S（PS）M6H ，但当我通过PCA-SM10作为参数时，它显示了两个记录，即PCA-SM10和PCA-SM10H。我只想要完全匹配的数据。
程序：
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 :
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
我该怎么做？
----------- -------------------------------------------------- -----------------
换句话说，
可以说有5个产品具有ProductCode，XX-ABC，XX-ABCD，XX-DEF，XX-DEFG，XX-W（XY）Z
所以我只想要那个具有精确ProductCode的产品，我是供应。
例如如果我通过XX-ABC，它应该只返回XX-ABC产品而不是XX-ABCD
但是你可以看到，有'（） '在产品代码中，所以我不能在WHERE子句中应用'='运算符。如果我在WHERE子句中使用'like %%'，则会导致问题，就像我将XX-ABC作为参数传递一样，因此输出将为XX-ABC& XX-ABCD。
我想要XX-ABC的记录和XX-W（XY）Z的记录
如何实现？
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 ?
推荐答案
您没有详细解释。我尝试了下面的片段，它没有任何问题，工作正常...
you are not explaining with enough details. I tried below snippet and it works fine without any issue...
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');
和where子句查询
And query with where clause
Declare @Value Varchar(100)
Set @Value = 'PCA-S(PS)M6H'
Select * From Test Where details = @Value
查看
http://sqlfiddle.com/ [ ^ ]：）
如果你想要一个
if you want exact one
declare @name nvarchar(max)
set @name= 'PCA-SM10'
select *From test where details like @name+'%'
这是一个奇怪的问题，因为'='应该按预期工作......
试试这个：
1.回到你之前的解决方案：
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.声明@MyParameter为 NVARCHAR ，而不是VARCHAR。
让我知道结果。
2. Declare @MyParameter as NVARCHAR, not VARCHAR.
Let me know the result.
这篇关于SQL查询 - '='和'％'之间的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
