Perhaps you're question is answered by Solution 1? But I get a different question from your title. And my proposal is a solution to multi-line data that I used to transform that "rowset" type of table, in an input file for instance, into a single-line "one record" for per-line TSQL table:
CREATE TABLE [instance].[schema].[oneliner](
[boxcarred][nvarchar](MAX)
)
CREATE TABLE [instance].[schema].[onelinerIdx](
[idx][int]IDENTITY(1,1),
[boxcarred][nvarchar](MAX)
)
BULK INSERT [instance].[schema].[oneliner] FROM 'C:\users\clrue\MFLL.txt'
WITH (
CODEPAGE = 'ACP'
)
INSERT INTO [instance].[schema].[onelinerIdx]
SELECT [boxcarred] FROM [instance].[schema].[oneliner]
Once in the table, the data is either one line or multiple lines, right?
Now create one more table, and filter that last indexed single/multi through a REPLACE ...
CREATE TABLE [instance].[schema].[nohammer](
[idx][int],
[seetosearch][nvarchar](MAX)
)
INSERT INTO [instance].[schema].[nohammer]
SELECT [idx], REPLACE([seetosearch],CHAR(13)+CHAR(10),' ') FROM [instance].[schema].[onelinerIdx]
Now search this target "string" usg whatever. Get it? Not a table now, just a string ...