CODEWITHSUNDEEP
sqlsql-serverwildcard
user3115933
user3115933

Reputation: 4443

What is wrong with my SQL syntax for searching a String in a column based on the String from another column?

I am using SQL Server 2014 and I have the following T-SQL query:

USE [MyDatabase]

SELECT [FirstName], [Email]

FROM [t1]

WHERE [Email] like '%'  + [FirstName] +  '%'

I would like to extract all records where [FirstName] appears in the [Email]. However, my query is not giving me the expected output.

I have written my "WHERE" filter based on an answer by Gordon Linoff in the following question:

SQL Search string from a column in another column

I feel I am missing something but I just can't figure where I am going wrong.

Any help would be much appreciated.

Sample:

FirstName         Email
Michelle          [email protected]

Upvotes: 0

Views: 72

Answers (3)

swati
swati

Reputation: 9

I think you need to do this

SELECT [FirstName], [Email]

FROM [t1]

WHERE [Email] like '%' || [FirstName]  || '%'

Upvotes: 0

karrb
karrb

Reputation: 36

SELECT t.[FirstName], t.[Email]
FROM t1 AS t
WHERE CHARINDEX(t.[FirstName], t.[Email]) > 0

Returns 0 if not found. Returns first index if found. For example:

FirstName: foo
Email: [email protected]

will return 1.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415800

I would like to extract all records where [FirstName] appears in the [Email].

If so, then this:

WHERE [FirstName]  like '%'  + [Email] +  '%'

Looks backwards to me, and you really want this:

WHERE [Email]  like '%'  + [FirstName] +  '%'

Upvotes: 2

Related Questions