asked on

T-SQL - select test1 from tbl_2 where [message] LIKE '%'+CAST((@test1) AS VARCHAR)+'%'

Hi Guys,
I need help on like operator below

declare @test1  int;  
declare @test2  sql_variant;

set @test1 = (select top 1 docid from tbl_1)    --result output:  only digits
set @test2 = (select * from tbl_2 where  [message] LIKE '%'+CAST((@test1) AS VARCHAR)+'%'));

Open in new window

How to select 'test1' result only from tbl_2 when has been founded in [message] column
-- select ***test1*** from tbl_2 where [message] LIKE '%'+CAST((@test1) AS VARCHAR)+'%'

Thank You!

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Darius

ASKER

Hi Pawan,
Something wrong here. Please review...

This is possible text in the message column:
DownloadMessage called with authentication token , message id [0000000000012345678] from [unknown]...
DownloadMessage called with authentication token , message id [0000000000050121345] from [unknown]...
DownloadMessage called with authentication token , message id [0000000000034215555] from [unknown]...
DownloadMessage called with authentication token , message id [0000000000053415615] from [unknown]...
DownloadMessage called with authentication token , message id [0000000000050144425] from [unknown]...
. . . . . .
. . . . . .

For example I need to find is a number (12345678)exist in the message content and execute.

SELECT * FROM tbl_2 where [message] LIKE ' . . . . . . . . . . . . . . .
returns whole text where is the number 12345678
I need to execute number only from tbl_2

Darius

ASKER

I thinking now different way:

if number true in tbl_2 so i can select number from tbl_1
???

Pawan Kumar

checking sir..

Pawan Kumar

Hi Darius,
Please try below-

DECLARE @test1 VARCHAR(1000)
DECLARE @SQL VARCHAR(MAX)

SELECT TOP 1 @test1 = SUBSTRING(@test1, CHARINDEX('message id [',@test1,0)+LEN('message id [') , 19) FROM tbl_1

SET @SQL = ' SELECT * FROM tbl_2 where [message] LIKE ' + '''' + '%' + CAST(@test1 AS VARCHAR(1000)) + '%' + ''''

EXEC (@SQL)

Open in new window

Hope it helps!

Darius

ASKER

Pawan,

DECLARE @test1 VARCHAR(1000) = '12345678'
DECLARE @SQL VARCHAR(MAX);

SELECT top 1 @test1 = SUBSTRING(@test1, CHARINDEX('message id [',@test1,0)+LEN('message id [') , 19) FROM tbl_1

SET @SQL = ' SELECT * FROM tbl_2 where [message] LIKE ' + '''' + 'DownloadMessage %%' + CAST(@test1 AS VARCHAR(1000)) + '%' + ''''

EXEC (@SQL)

Open in new window

Output results all records
DownloadMessage called with authentication token , message id [0000000000012345678] from [unknown]...
DownloadMessage called with authentication token , message id [0000000000050121345] from [unknown]...
DownloadMessage called with authentication token , message id [0000000000034215555] from [unknown]...
DownloadMessage called with authentication token , message id [0000000000053415615] from [unknown]...
DownloadMessage called with authentication token , message id [0000000000050144425] from [unknown]...
. . . . . .
. . . . . .

Expected this number only 12345678

Where is my mistake? :(

Pawan Kumar

Hi,

Please try this ..

DECLARE @test1 VARCHAR(1000)
DECLARE @SQL VARCHAR(MAX)

SELECT TOP 1 @test1 = SUBSTRING(@test1, CHARINDEX('message id [',@test1,0)+LEN('message id [') , 19) FROM tbl_1

SET @SQL = ' SELECT * FROM tbl_2 where [message] LIKE ' + '''' + '%' + CAST(@test1 AS VARCHAR(1000)) + '%' + ''''
+ 'AND CHARINDEX(''DownloadMessage'', [message] , 0 ) > 0 '

EXEC (@SQL)

Open in new window

Hope it helps!

Darius

ASKER

Hi Pawan,
not working as I expecting. Output result: all records extracted...
Sorry for make this complicated to you. I closing this question and opening new...

Thanks

Darius

ASKER

Pawan have been very helpful and i believe he provide the best solution...

T-SQL - select ***test1*** from tbl_2 where [message] LIKE '%'+CAST((@test1) AS VARCHAR)+'%'

T-SQL - select test1 from tbl_2 where [message] LIKE '%'+CAST((@test1) AS VARCHAR)+'%'