CODEWITHSUNDEEP
sqlsql-server
Stuart
Stuart

Reputation: 4288

SQL to find a string in another string

I have 2 tables:

tblFoo:

Id | MessageId | SenderName

tblBar:

MessageId | Name

I need to update all the rows in tblFoo with the MessageId from tblBar where SenderName starts with Name. e.g.

tblFoo:

Id     | MessageId | SenderName
-------+-----------+--------------------
365175 | NULL      | x.y.z.abc.def.ghi
535646 | NULL      | a.b.c.fjdkf.dsfjdsf:a
535647 | NULL      | a.b.c.sdjskfj.dsfjds:b

tblBar:

MessageId                             | Name
--------------------------------------+--------
9B30FCD1-F063-4B7F-887B-65288C3681D3  | x.y.z
56FBEF0F-8C29-4614-8894-4F64A515659A  | a.b.c

So in tblFoo Id=365175 would end up with MessageId = 9B30FCD1-F063-4B7F-887B-65288C3681D3 and Id=535646 would end up with MessageId = 56FBEF0F-8C29-4614-8894-4F64A515659A and Id=535647 would also end up with MessageId= 56FBEF0F-8C29-4614-8894-4F64A515659A

So far I have 

begin transaction
    update tblFoo
    set MessageId = 
    (
        select MessageId
        from tblBar
        where [Name] ???
    );
    rollback transaction

I can't do

where [Name] like

as [Name] is a substring of SenderName.

Or do I need to use a temp table?

Upvotes: 2

Views: 437

Answers (2)

Coder1991
Coder1991

Reputation: 735

You can use the inner join as below.

update foo set messageid = br.messageid
from
tblFoo foo
inner join 
tblBar br 
on foo.senderName like ltrim(br.Name) + '%'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Your code looks like SQL Server. You can do this using something like:

update h
    set MessageDefinitionId = (select MessageDefinitionId
                               from Sentilan2.msg.tblMessageDefinition md
                               where h.SenderName like md.[Name] + '%'
                              );
    from h;

Your code is rather hard to follow because you define tables tblFoo and tblBar, but the sample code uses different table names.

Also note that this assumes that there is only one match. You might want top 1 in the subquery to avoid run-time errors.

Upvotes: 0

Related Questions