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

Question

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.

Answer 1

How about using LEFT?

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

• SQLFIddle Demo