Comparing two variables

Transact-SQL
1

Hi all,

Is there a way that I can use 'like' statement instead of '=' where I am comparing two values in the following SQL ? I do not want the full match, but if it matches for only the characters and not the numeric values retrieved.

select distinct
tocs.name [OS_FUN_25 MOF MOF CombinedMeasurements Check],
ocs2.name [Referenced Sets w/ CombinedMeasurements],
imap.dataitemcode + cast (imap.destoccnum as varchar) [Map From:],
imap.multifielddataitemcode + cast (imap.multifieldoccnum as varchar) [Mapped to: Parent] ,

case when (imap.dataitemcode + cast (imap.destoccnum as varchar)) =
(imap.multifielddataitemcode + cast (imap.multifieldoccnum as varchar))
then 'PASS' else 'FAIL' end [Result]
from
#TestSample_Ocs tocs

2

Would be useful if you can post sample input data and your expected output (i.e., cases where there should be a match and cases where there should not be). Are the numeric digits at the end, the beginning, or are they interspersed?

BTW, when you use VARCHAR, always specify a length (such as VARCHAR(32), VARCHAR(MAX) etc.) Simply using VARCHAR without giving it a length can have some unintended consequences.

1 Like
3

The numeric characters are at the end . .so just have to compare the non numeric .

4

Easiest is to use a CROSS APPLY to assign alias names to the concatenated values, so you don't have to repeat the concatenation over and over:

select distinct
tocs.name [OS_FUN_25 MOF MOF CombinedMeasurements Check],
ocs2.name [Referenced Sets w/ CombinedMeasurements], 
imap.dataitemcode + cast (imap.destoccnum as varchar) [Map From:],
imap.multifielddataitemcode + cast (imap.multifieldoccnum as varchar) [Mapped to: Parent] ,

/*ignore everything past first digit when comparing*/
case when (left(ca1.code1, patindex('%[0-9]%', ca1.code1 + '0')) =
left(ca1.code2, patindex('%[0-9]%', ca1.code2 + '0')
then 'PASS' else 'FAIL' end [Result]
from ...
cross apply (
    select imap.dataitemcode + cast (imap.destoccnum as varchar(30)) as code1,
        imap.multifielddataitemcode + cast (imap.multifieldoccnum as varchar(30)) as code2
) as ca1
1 Like
5

Just getting syntax error, when I add this

Msg 156, Level 15, State 1, Line 1936
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Line 1951
Incorrect syntax near the keyword 'as'.

select distinct
tocs.name [OS_FUN_25 MOF MOF CombinedMeasurements Check],
ocs2.name [Referenced Sets w/ CombinedMeasurements],
imap.dataitemcode + cast (imap.destoccnum as varchar) [Map From:],
imap.multifielddataitemcode + cast (imap.multifieldoccnum as varchar) [Mapped to: Parent] ,

/ignore everything past first digit when comparing/
case when (left(ca1.code1, patindex('%[0-9]%', ca1.code1 + '0')) =
left(ca1.code2, patindex('%[0-9]%', ca1.code2 + '0'))
then 'PASS' else 'FAIL' end [Result]
from #TestSample_Ocs tocs

--join cv3order o on tocs.guid=o.ordersetguid
join #TestSample_O o on tocs.guid=o.ordersetguid
join cv3orderentryform oef on oef.formguid=tocs.orderentryformguid and oef.iscurrent=1
join cv3orderentryfield field on field.orderentryformguid=oef.guid and field.dataitemcode like 'CombinedMeasurements%'
left join cv3ordercatalogset ocs2 ON o.ordercatalogmasteritemguid = ocs2.guid
join cv3orderentryform oef2 on ocs2.orderentryformguid=oef2.formguid and oef2.iscurrent=1
join cv3orderentryfield field2 on field2.orderentryformguid=oef2.guid and field2.dataitemcode like 'CombinedMeasurements%'
left outer JOIN cv3ordersetitemfieldmap imap ON imap.orderguid = o.guid AND field.dataitemcode = imap.dataitemcode and field.multifieldoccnum=imap.multifieldoccnum

cross apply (
select imap.dataitemcode + cast (imap.destoccnum as varchar(30)) as code1,
imap.multifielddataitemcode + cast (imap.multifieldoccnum as varchar(30)) as code2
) as ca1

6

Slight misalignment in the brackets.

.....
/*ignore everything past first digit when comparing*/
case when left(ca1.code1, patindex('%[0-9]%', ca1.code1 + '0')) =
left(ca1.code2, patindex('%[0-9]%', ca1.code2 + '0'))
then 'PASS' else 'FAIL' end [Result]   
from ...  
....
1 Like
7

Thank you very much . .. Part of a stored Proc, I am testing :slight_smile:

8

Correction!:

case when left(ca1.code1, patindex('%[0-9]%', ca1.code1 + '0') - 1) =
left(ca1.code2, patindex('%[0-9]%', ca1.code2 + '0') - 1)
then 'PASS' else 'FAIL' end [Result]
from ...
....
[/quote]

1 Like
9

There you go . . my test was a fail . .. corrected and its good I believe . . :slight_smile:

10

Pasted image1042×141 5.82 KB

was executing a fail test and displaying NULL instead of the values.

11

That's not related to the CASE statement. There must be NULL values in the table itself.

1 Like
12

Is there a way to display the values instead of showing NULL

13

Are you trying to have a default value appear when your dataset contains NULL? Is so you can use ISNULL to define your default value. If you don't expect NULLs to show up you'll need to check your underlying data.

1 Like