SQL – Searching the column that has multiple lines

When typing information into a text/memo field on a webpage, the end-user sometimes press the “Enter” button. This resulted the text/information being split into several lines, which were then saved in databases. There will be some other reasons too, for multiple lines in a column.

The information seems to be in a single line when the data is retrieved, but when we search those columns, no rows are returned.

In such scenarios, we can use CHAR(10) or CHAR(13) to indicate the break, or we can just use the percent (percentage) wildcard.

Look at the below example:

CREATE TABLE tbSearchMultiLines(
IDCol INT,
TxtCol VARCHAR(MAX));

INSERT INTO tbSearchMultiLines VALUES(10,’Age:
Between 25-40
Experience
:10 Years
CTC (USD)
:90000′),
(11,’Age:
Between 25-40
Experience
:15 Years
CTC (USD)
:120000′)

SELECT * FROM tbSearchMultiLines

Now, if we try to search the string the way it looks in the result in the where clause, it will not return any result.

SELECT * FROM tbSearchMultiLines WHERE
TxtCol = ‘Age: Between 25-40 Experience :10 Years CTC (USD) :90000’

SELECT * FROM tbSearchMultiLines WHERE
TxtCol LIKE ‘%Age: Between 25-40 Experience :10 Years CTC (USD) :90000%’

SELECT * FROM tbSearchMultiLines WHERE
TxtCol LIKE ‘%CTC (USD) :90000%’

As discussed above, there are two ways to search the string: Using wild card and using CHAR() function.

Using wild-card:

SELECT * FROM tbSearchMultiLines WHERE
TxtCol LIKE ‘%Age:%Between 25-40%Experience%:10 Years%CTC (USD)%:90000%’

SELECT * FROM tbSearchMultiLines WHERE
TxtCol LIKE ‘%CTC (USD)%:90000%’

Using CHAR():

SELECT * FROM tbSearchMultiLines WHERE
TxtCol LIKE
‘%Age:%’+CHAR(10)+
‘%Between 25-40%’+CHAR(10)+
‘%Experience%’+CHAR(10)+
‘%:10 Years%’+CHAR(10)+
‘%CTC (USD)%’+CHAR(10)+
‘%:90000%’

SELECT * FROM tbSearchMultiLines WHERE
TxtCol LIKE ‘%CTC (USD)%’+CHAR(10)+’%:90000%’

SELECT * FROM tbSearchMultiLines WHERE
TxtCol LIKE
‘%Age:%’+CHAR(13)+
‘%Between 25-40%’+CHAR(13)+
‘%Experience%’+CHAR(13)+
‘%:10 Years%’+CHAR(13)+
‘%CTC (USD)%’+CHAR(13)+
‘%:90000%’

Hope you find this article helpful.

Please do follow this blog for interesting updates.