Substring Function for Specific Value Assignment

[larrydavid]

Hello,

I have this statement:

UPDATE  tbl_Test
SET     COLLECTOR = 
	CASE	WHEN Substring(tbl_Test.TIN, 9, 1) <= 3 THEN 'Test_User_1' 
		 --WHEN Substring(tbl_Test.TIN, 9, 1) IN (4,5) THEN 'Test_User_2'
			ELSE 'Test_User_3' 
	END
FROM    tbl_Test
WHERE   Test_Owner = 'RCR' 
 	SUBSTRING(tbl_Test.TIN, 9, 1) NOT IN 
	('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z')

The idea here is to assign users to TIN numbers that fall within these ranges:

0,1,2,3 = Test_User_1
4,5 = Test_User_2
6,7,8,9 = Test_User_3

So my question is, am I doing this right? Will the IN (4,5) work for Test_User_2?

I am seeing inconsistent results so I am not sure this is the best approach.

Thanks,
Larry

 

[simian336]

Might help if we had a data sample and desired result.

Simi

 

[Qik3Coder]

I second simian.

The only thing i can sugest without data is that you have leading spaces that are throwing your numbers.

Some sample data, show what IS being set, will help.

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[larrydavid]

Hello,

Here is the sample data. It is an 11-digit value of type nvarchar:

94328165701
77051070900
95360636700
33028911500
77051070900
95392326500
95164333600
33083034604
33083034604

Thanks,
Larry

 

[larrydavid]

Hello,

Never mind, I confirmed I'm getting the correct results.

Thanks,
Larry

 

[Qik3Coder]

Works fine here. I'd check that Alpha numeric bit in your where clause.

Modified source data to get results for all conditions.

Select TIN, Substring(tbl_Test.TIN, 9, 1), CASE    
WHEN Substring(tbl_Test.TIN, 9, 1) <= 3 THEN 'Test_User_1'          
WHEN Substring(tbl_Test.TIN, 9, 1) IN (4,5) THEN 'Test_User_2'            
ELSE 'Test_User_3'     END
FROM (
Select '94328165701' TIN UNION ALL
Select '77051070900' UNION ALL
Select '95360636700' UNION ALL
Select '33028911100' UNION ALL
Select '77051070900' UNION ALL
Select '95392326506' UNION ALL
Select '95164333600' UNION ALL
Select '33083034604' UNION ALL
Select '33083034604') as tbl_test
ORDER BY Substring(tbl_Test.TIN, 9, 1)

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[gmmastros]

I would suggest a couple minor changes.

UPDATE  tbl_Test
SET     COLLECTOR = 
          CASE WHEN Substring(tbl_Test.TIN, 9, 1) <= [!]'3'['/!] THEN 'Test_User_1' 
               WHEN Substring(tbl_Test.TIN, 9, 1) IN [!]('4','5') [/!]THEN 'Test_User_2'
               ELSE 'Test_User_3' 
          END
FROM    tbl_Test
WHERE   Test_Owner = 'RCR' 
        And SUBSTRING(tbl_Test.TIN, 9, 1) Between '0' And '9'

Notice the changes... I put single quotes in the code. This will cause string comparisons because both sides of the equal sign are strings. Also notice that I changed the where clause to use a between operator but still using string comparisons.

The way you originally had it written, the strings were converted to integers for the comparison. This isn't too bad but it's likely to be a couple nano-seconds slower for each comparison.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom