CODEWITHSUNDEEP
t-sqlsql-server-2012string-comparison
NePh
NePh

Reputation: 996

Ignore last character when comparing strings

I have following strings I want to compare:

DECLARE @a VARCHAR(20), @b VARCHAR(20) 

SET @a = '05Y2203-B10'
--SET @a = '05Y2203-B10B'
SET @b = '05Y2203-B10C'

When comparing, I want to ignore the last character, but only if the length of the substring after the - is 4.

'05Y2203-B10B'   --ignore the 'B'
'05Y2203-B10'    --do not ignore the '0'

A few examples:

'05Y2203-B10B' = '05Y2203-B10B' --match
'05Y2203-B10B' = '05Y2203-B10C' --match
'05Y2203-B10'  = '05Y2203-B10B' --match
'05Y2203-B10'  = '05Y2203-B11'  --no match
'05Y2203-B10'  = '18G9987-B10'  --no match

The strings will always look like one of the following patterns:

'%-[A-Z][0-9][0-9]'      --without last character
'%-[A-Z][0-9][0-9][A-Z]' --with last character

The length of the substring before the - can vary.

So far I have this solution:

SELECT  1
WHERE   CASE WHEN LEN(SUBSTRING(@a, CHARINDEX('-', @a) + 1, 4)) = 4 
             THEN SUBSTRING(@a, 1, LEN(@a) - 1)
             ELSE @a
        END 
        = 
        CASE WHEN LEN(SUBSTRING(@b, CHARINDEX('-', @b) + 1, 4)) = 4 
             THEN SUBSTRING(@b, 1, LEN(@b) - 1)
             ELSE @b
        END

This works but it's not quite readable, especially if there are more conditions in the query.

Is there a simpler or more elegant solution for this problem?

Upvotes: 1

Views: 584

Answers (2)

Reza
Reza

Reputation: 19843

As I understand you want more readable (maintainable) query, if so then you can write a simple scalar value function to return the string part you want and hide all of the complexity in that function

Also for having better performance you can define another computed column for this trimmed data and store it using that function without trailing data.
The column gets physically stored if you mark it with PERSISTED.
When you run query use this column.

Upvotes: 2

qxg
qxg

Reputation: 7036

Since the length of sub-string after - is either 3 or 4, you just only fetch 3 characters after -. Here is code snippet

LEFT(@a, CHARINDEX('-', @a) + 3) = LEFT(@b, CHARINDEX('-', @b) + 3)

Upvotes: 2

Related Questions