Select query with where condition which contain next line in that string

Transact-SQL
1

Hi,
Currently i am faced with one problem in select query with where condition which contain next line in that string.

Let me explain : One of the customer have excel sheet which contain address like 2 to 3 lines, what they did , just copy from excel and paste in sql table, so when copy that string from that row and paste in notepad, it same in in excel sheet, i mean it contain 2 to 3 lines. So in select they give where condition as one line, the result is not coming.

Ex : one of the columns contains the following line, 2 lines in the same cell.

This is a string
with a line break

If I search with the condition as a separate line I can get the result %This is a string% or %with a line break%.

But i f I search with the whole line I can't get the result %This is a string with a line break%.

Is separate line got result
SELECT * FROM Test1 WHERE Datya LIKE '%This is a string%'

If one line in where condition, no result
SELECT * FROM Test1 WHERE Datya LIKE '%This is a string with a line break%'

Please reply asap. So how to select the string in sql with the new line or next line ?

Note: we can't insert as one line if got multiple line, bcz its address.

Regards
Aravind

2

It will likely slow down the search, but you could use REPLACEs on the column being searched:

WHERE REPLACE(REPLACE(Datya, CHAR(13), ''), CHAR(10), ' ') LIKE '%This is a string with a line break%