CODEWITHSUNDEEP
sqlsql-server
David Austin
David Austin

Reputation: 95

How can I use the LIKE operator to make this match in SQL Server?

I am trying to match a price string, like $25.00, to find the corresponding currency symbol. For example, $25.00 should match USD. This much is working; however when I pass in 25.00 (no currency symbol), then I have an unwanted match on CUP.

I have the following table set up in SQL Server 2012:

CurrencyId  varchar(3)
Symbol      nvarchar

Here is some of the data:

Currency Symbol
ANG      ƒ
CUP      ₱
EUR      €
USD      $

The query is:

SELECT [t0].[CurrencyId], [t0].[Symbol]
FROM [dbo].[EWN_Currency] AS [t0]
WHERE '25.00' LIKE '%'+[t0].[Symbol]+'%'

If I skip the string concatenation for testing, then it at least doesn't return the bad match, such as:

SELECT [t0].[CurrencyId], [t0].[Symbol]
FROM [dbo].[EWN_Currency] AS [t0]
WHERE '25.00' LIKE '%₱%'

It seems the string concatenation isn't setup correctly with LIKE '%'+[t0].[Symbol]+'%'. I've played with converting everything to nvarchar without luck. How would I make this work? Thanks.

Upvotes: 3

Views: 1118

Answers (2)

user359040
user359040

Reputation:

Try:

with cte as
(select [Symbol], [CurrencyId] from [dbo].[EWN_Currency] where [Symbol] = '₱')
SELECT coalesce([t0].[CurrencyId], cte.[CurrencyId]) [CurrencyId],
       coalesce([t0].[Symbol], cte.[Symbol]) [Symbol]
FROM cte
LEFT JOIN [dbo].[EWN_Currency] AS [t0]
       ON '25.00' LIKE '%'+[t0].[Symbol]+'%'

Note: If you intend to use this with a table of currency-formatted data rather than a single string then I suggest cross joining from the cte to your data table, then left joining from the data table to the currency table.

(SQLFiddle here)

Upvotes: 0

John Woo
John Woo

Reputation: 263803

How about using LEFT?

SELECT *
FROM   TableName
WHERE  LEFT('$25.00',1) = Symbol

Upvotes: 2

Related Questions