Need to search special characters from Table

Question

question

AmrutKumbar asked Mar 31, '20

Need to search special characters from Table

Table1

IDColumn11[1-100]2Amrut got 11% hike3Am's4[1-100]5[amrut]6[1-100-200]7[amrut] is good boy

I have a table as above.

Case 1: If i search column1="[1-100]"

expected: I should get only 1st and 4th row

Case 2: if i search column1st contains "[1-100]"

expected: I should get only 1st and 4th row

Case 3: If i search column1 begins with "[1-100]"

expected: I should get only 1st and 4th row

Case 4: If I search column1 begins contains "[1-10"

expected: I should get 1st,4th & 6th row.

Case 5: If I search column1 for "[amrut]"

expected: 5th row for equalsto

5th & 7th for contains and begins with operater

sql server 2016

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-04-01T11:03:19Z

anthony.green answered Apr 1, '20

You need to escape the first [ in the string either use [ ] to encapsulate the first [, like [[] or use an escape character and tell SQL the escape.

create table #amrut (id int, column1 varchar(50))
insert into #amrut values
(1,'[1-100]'),
(2,'Amrut got 11% hike'),
(3,'Am''s'),
(4,'[1-100]'),
(5,'[amrut]'),
(6,'[1-100-200]'),
(7,'[amrut] is good boy')
select * from #amrut where column1 like '%[[]1-100]%'
select * from #amrut where column1 like '%[[]1-10%'
select * from #amrut where column1 like '%[[]amrut]%'
select * from #amrut where column1 like '%\[1-100]%' escape '\'
select * from #amrut where column1 like '%\[1-10%' escape '\'
select * from #amrut where column1 like '%\[amrut]%' escape '\'

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question