SQL查询 - '='和'％'之间的问题

问题描述

我在表格中有以下类型的记录（这里只显示一个字段），

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 ?

推荐答案

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

查看

[]：）

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

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

2.声明@MyParameter为 NVARCHAR ，而不是VARCHAR。



让我知道结果。

2. Declare @MyParameter as NVARCHAR, not VARCHAR.

Let me know the result.

这篇关于SQL查询 - '='和'％'之间的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！