cancel
Showing results for 
Search instead for 
Did you mean: 

Where Clause in ntext/text datatype

former_member541807
Active Contributor
0 Kudos

hi all,

i have this simple query, not using a variable - this run okey

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T2.[LineText]

FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN POR10 T2 ON T0.DocEntry = T2.DocEntry

WHERE T2.[LineText]  Like '%%TEST%%'

but when i use the same query but this time i am using a variable, it throws an error

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T2.[LineText]

FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN POR10 T2 ON T0.DocEntry = T2.DocEntry

WHERE T2.[LineText]  Like '%%[%0]%%'

how you do it? pls help

regards,

Fidel

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

Please try this query :

DECLARE @LINETEXT as VARCHAR(MAX)

SELECT TOP 1 @LINETEXT = R0.[LineText]  FROM [dbo].[POR10] R0 WHERE CAST(R0.[LineText] as  VARCHAR(MAX) )  = '[%0]'

SELECT @LINETEXT='[%0]'

  

    SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price],     T2.[LineText] 

    FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN POR10 T2 ON T0.DocEntry = T2.DocEntry  

    WHERE CAST(T2.[LineText] as VARCHAR(MAX))   Like '%%@LINETEXT%%'  

Salamat.

Regards,

Clint