SQL 2014 LIKE Statement Not Working Like I Want

Other SQL Server Topics
1

UPDATE WKLDStaging.RadioWorkRTI10
SET orgloc = 'Origin Telco'

WHERE (select Top 1 devicename from WKLDStaging.RadioGeography where devicename like CONCAT('%',orgloc,'%') ) Like CONCAT('%',orgloc,'%')

What I want above statement to do is:
Find that devicename in table WKLDStaging.RadioGeography that is somewhere in the field called orgloc.
No matter what I have tried, I cannot get it to work.
Not sure if you can use a LIKE over a field.

Example:
WKLDStaging.RadioGeography = device name = Taschereau RTI
WKLDStaging.RadioWorkRTI10 =
orgloc = (8197962329 RTI 4157(Taschereau RTI))HDX-2 Telco

2

This does not work either:

WHERE (select Top 1 devicename from WKLDStaging.RadioGeography where devicename like '%' + Orgloc + '%' ) Like '%' + Orgloc + '%'

3

Even this does not find it:

select * from WKLDStaging.RadioWorkRTI10
WHERE 'Taschereau RTI' LIKE '%' + WKLDStaging.RadioWorkRTI10.Orgloc + '%'

But it is in Orgloc:
(8197962329 RTI 4157(Taschereau RTI))HDX-2 Telco

4

This works, but that is because there are records in the table where the Orgloc only = Origin Telco

select * from WKLDStaging.RadioWorkRTI10
WHERE 'Origin Telco' LIKE '%' + WKLDStaging.RadioWorkRTI10.Orgloc + '%'

5

Not sure if this it's what you're expecting , but here it's an example , that will clear it.

create table #RadioWorkRTI10
(idField int identity(1,1)
,Orgloc varchar(100))

insert into #RadioWorkRTI10(Orgloc)
values('Origin Telco')
,('Taschereau RTI')
,('(8197962329 RTI 4157(Taschereau RTI))HDX-2 Telco')

Select no1:

select *
from #RadioWorkRTI10
where Orgloc like '%Taschereau RTI%'
idField Orgloc
2 Taschereau RTI
3 (8197962329 RTI 4157(Taschereau RTI))HDX-2 Telco

select no2:

select *
from #RadioWorkRTI10
where Orgloc like '%Telco%'
idField Orgloc
1 Origin Telco
3 (8197962329 RTI 4157(Taschereau RTI))HDX-2 Telco
1 Like
6

select *
from #RadioWorkRTI10
where Orgloc like '%Taschereau RTI%'

7

I need LIKE to work as below:

WHERE 'Taschereau RTI' like CONCAT('%',orgloc,'%')

I trying to do a LIKE % % over a field name.
The field orgloc contains the phrase 'Taschereau RTI' somewhere in it.

orgloc = (8197962329 RTI 4157(Taschereau RTI))HDX-2 Telco

8

And so the solution from @stepson gives you exactly that

9

Sorry not explaining myself correctly.

WHERE (select Top 1 devicename from WKLDStaging.RadioGeography where devicename like CONCAT('%',orgloc,'%') ) Like CONCAT('%',orgloc,'%')

I need to compare field name devicename to field name orgloc
For example, field name devicename contains data Taschereau RTI (which is a device name)
and field name orgloc contains data (8197962329 RTI 4157(Taschereau RTI))HDX-2 Telco
Now how do I find that device name inside the field orgloc by comparing field name to field name.

10

Sorry showing code incorrectly, should be:

UPDATE WKLDStaging.RadioWorkRTI10
SET orgloc = devicename

WHERE (select Top 1 devicename from WKLDStaging.RadioGeography where devicename like CONCAT('%',orgloc,'%') )

So if Top 1 DeviceName in table WKLDStaging.RadioGeography is found somewhere in field orgloc then I want to SET orgloc = devicename

11

Sorry again. Below code is where it all starts.

UPDATE WKLDStaging.RadioWorkRTI10
SET orgloc = devicename
from WKLDStaging.RadioGeography
WHERE orgloc in(select Top 1 devicename from WKLDStaging.RadioGeography where devicename LIKE CONCAT('%',orgloc,'%'))

12

Trying to find DeviceName inside of orgloc by using LIKE and only field names.

13




this does not work either:

where devicename LIKE '%' + orgloc + '%')

14

Please provide:

  • table description of both tables in question, in the form of create statements
  • sample data in the form of insert statements
  • description of how the two tables are linked
  • expected output from the sample data you provide
2 Likes
15 
create table #RadioWorkRTI10
(idField int identity(1,1)
,Orgloc varchar(100))

create table #RadioGeography
(idG int identity(1,1)
,devicename varchar(100))


insert into #RadioWorkRTI10(Orgloc)
values('Origin Telco')
,('Taschereau RTI')
,('(8197962329 RTI 4157(Taschereau RTI))HDX-2 Telco')

insert into #RadioGeography(devicename)
values('Taschereau RTI')
,('test 2 - Not matching value')

select no1

select *
from  #RadioWorkRTI10 as W
   inner join #RadioGeography AS G
   on  w.orgloc like '%'+g.devicename+'%'
idField Orgloc idG devicename
2 Taschereau RTI 1 Taschereau RTI
3 (8197962329 RTI 4157(Taschereau RTI))HDX-2 Telco 1 Taschereau RTI

or select no2

select w.*
from  #RadioWorkRTI10 as W
where exists(select * from #RadioGeography AS G 
             where w.orgloc like '%'+g.devicename+'%')
idField Orgloc
2 Taschereau RTI
3 (8197962329 RTI 4157(Taschereau RTI))HDX-2 Telco

Update statement /query

Update W
Set W.orgLoc = g.devicename
From #RadioWorkRTI10 as W
   inner join #RadioGeography AS G
   on  w.orgloc like '%'+g.devicename+'%'

output :

select * from #RadioWorkRTI10
idField Orgloc
1 Origin Telco
2 Taschereau RTI
3 Taschereau RTI
1 Like
16

stepson:

Your code below works perfectly. It does exactly what I need.
Thank You very much.

Update W
Set W.orgLoc = g.devicename
From #RadioWorkRTI10 as W
inner join #RadioGeography AS G
on w.orgloc like '%'+g.devicename+'%'

17

You're welcome!

ps: @cpudad, for a faster&better solution, please see the recomandations from @bitsmed