-2

I have the following table with '' as a special character:

Column 1 Column 2
1 13
2 1312
3 315
4 1613
5 15311

I want to select only those records where Column 2 has 3 between special characters. I have used a built-in function of my DB fn_SplitOneValue but it selects 3 and 13 both. Please help!

Dale K
  • 25,246
  • 15
  • 42
  • 71
Nauman Khan
  • 7
  • 3
  • 4
    Show your function code, and show your desired results. And seriously consider normalising your database. This form of storage will cause you issues for the life of the product. – Dale K Aug 23 '23 at 06:58
  • 3
    It wouldn't @DaleK, but `WHERE Column2 LIKE N'%3%'` might (the special characters are U+F8FD). – AlwaysLearning Aug 23 '23 at 07:07
  • 2
    But you also have to deal with the case that `3` is first or last in the delimited list - @nauman - this is not in first normal form. These values should be stored in a table with 10 rows not 5 for current data. (1 row for Column 1 = 1, 3 rows for Column 1 = 5 and 2 rows each for the rest) – Martin Smith Aug 23 '23 at 07:13
  • Yes @Martin, you are right it's not normalized. But it is what it is since I'm not the DB admin. – Nauman Khan Aug 23 '23 at 07:25
  • 1
    You need to show the query that you used. You are probably doing a `LIKE`, try using `=` – Squirrel Aug 23 '23 at 07:44
  • 1
    `WHERE CONCAT(N'', Column2, N'') LIKE N'%3%'` should do what you need with no need to call any split function – Martin Smith Aug 23 '23 at 08:04
  • If you are determined to use a "multi-valued" field why not use an industry standard like JSON? You gain inbuilt functions and simpler support in the long run. – Paul Maxwell Aug 23 '23 at 09:43

1 Answers1

0

try this

begin try 
create table #test ([Column 1] int, [Column 2] nvarchar(100))

insert into #test ([Column 1], [Column 2])
values (1, '13')
    ,(2, '1312')
    ,(3, '315')
    ,(4, '1613')
    ,(5, '15311')
end try
begin catch
end catch

select *
from #test
where charindex('3',[column 2],0)>0
Gufus
  • 418
  • 2
  • 7
  • 15