Search by nvarchar

Question

Hi all. I have this problem. In table I have column which is nvarcar type. and row in this column is row1= 1;6 row2 = 12 row3 =6;5;67 etc... I try to search this column. for example when i send 1 i try to get only row1. I use LIKE but in result set I get row1 and row2.

How can I achieved this, any help is appreciated. Tnx...

Answer 1

You have four possibilities for where the key can be:

• It could be the entire key
• At the start
• In the middle
• At the end

Therefore, you have to select for each distinct possibility, like so:

SELECT *
FROM MyTable
WHERE MyColumn = @Key
   OR MyColumn LIKE @Key + ';%'
   OR MyColumn LIKE '%;' + @Key + ';%'
   OR MyColumn LIKE '%;' + @Key

Answer 2

Not particulary elegant but this get's the job done.

DECLARE @Table TABLE (Row NVARCHAR(32))

INSERT INTO @Table VALUES ('1;6')
INSERT INTO @Table VALUES ('12')
INSERT INTO @Table VALUES ('6;5;67')

SELECT  *
FROM    @Table
WHERE   Row = '1'
        OR Row LIKE '%;1'
        OR Row LIKE '1;%'
        OR Row LIKE '%;1;%'

edit

follow up on your comment: this works in SQL Server 2005.

CREATE TABLE Analysis (Text1 NVARCHAR(32)) 
GO

CREATE PROCEDURE [dbo].[Test] @Key as nvarchar AS 
SELECT  * 
FROM    dbo.Analysis
WHERE   Text1 = @Key 
        OR Text1 LIKE '%;'+ @Key
        OR Text1 LIKE @Key + ';%' 
        OR Text1 LIKE '%;' + @Key + ';%' 
GO

EXEC Test '1'
DROP PROCEDURE Test
DROP TABLE Analysis

Answer 3

normalize your tables!, storing multiple values in a single column will cause you grief as long as you use this design. it might appear easier to store data this way, but as you are seeing it is difficult to query against this data, and the queries you do create will be awful and have no chance of using an index.