SQL to find a string in another string

Question

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?

Is it possible, that there are more than one entries in tblBar, where the Name matches the SenderName? However, you are looking for something like LIKE SenderName=tblBar.Name + '%'... — Gottfried Lesigang
– Gottfried Lesigang, Commented Jun 7, 2017 at 11:52

Coder1991 · Accepted Answer · 2017-06-07 12:31:44Z

1

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) + '%'

answered Jun 7, 2017 at 12:31

Coder1991

7351 gold badge5 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-06-07 11:52:18Z

0

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.

answered Jun 7, 2017 at 11:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL to find a string in another string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related