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

CreateTableStmt

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%’

NoResultsInSearch

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%’

SearchwithWildCard

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%’

SearcwithChar

Hope you find this article helpful.

Please do follow this blog for interesting updates.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s