T_SQL Question Not finding a a record with a space in the middle of the string

Question

T_SQL Question Not finding a a record with a space in the middle of the string

Resender

SSCertifiable

Points: 7443
More actions
May 19, 2015 at 8:18 am

#308319

Ok following scenario
create table dbo.ADAssets
(Computername nvarchar(max))
insert into dbo.ADAssets
values
('AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565')
So if execute the following
select Computername
from dbo.ADAssets
where Computername like
'AIRLBEOF3565%'
I get both records,but if I do this
select *
from dbo.ADAssets
where Computername in
(
'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565'
)
I only get AIRLBEOF3565
So the big picture is that I need to compare 2 tables to find records that match & don't but that I get matches that shouldn't be & matches that aren't

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

AlexSQLForums SSChampion Points: 14277 More actions · Answer 1

Resender (5/19/2015)
Ok following scenario
create table dbo.ADAssets
(Computername nvarchar(max))
insert into dbo.ADAssets
values
('AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565')
So if execute the following
select Computername
from dbo.ADAssets
where Computername like
'AIRLBEOF3565%'
I get both records,but if I do this
select *
from dbo.ADAssets
where Computername in
(
'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565'
)
I only get AIRLBEOF3565
So the big picture is that I need to compare 2 tables to find records that match & don't but that I get matches that shouldn't be & matches that aren't

First of all get rid of nvarchar(max).

Alex S

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Please will you check your INSERT statement? You're trying to insert two columns into a single-column table.

John

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Yep, if you insert the data correctly, both queries work:

create table dbo.ADAssets

(Computername sysname)

insert into dbo.ADAssets

values

(N'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d'),

(N'AIRLBEOF3565');

select Computername

from dbo.ADAssets

where Computername like

'AIRLBEOF3565%';

select *

from dbo.ADAssets

where Computername in

(

'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',

'AIRLBEOF3565'

);

Resender SSCertifiable Points: 7443 More actions · Answer 4

OK,that indeed seems to work,I initially wanted to ask this question using multiple columns & then when I finished writing the post realised I overcomplicated my question,so I simplified the statements for the create table but forgot the insert.