CODEWITHSUNDEEP
sqlsql-serverstringselectcase
rjohns68
rjohns68

Reputation: 11

Distinguish between 1000 and 10000 in Select LEFT for groupings

I currently have a Select LEFT that is grabbing the 1st Left digit of an ID field. This has been working fine so long as our ID number have been 4 digits (0 to 9999) however we are now introducing 5 digit numbers in the 10,000 range and the current Select LEFT can not tell the difference between 1,000 and 10,000. The purpose of selecting the first digit (s) is to group the numbers by the thousands (1000, 2000, 3000, . . . , 100000). What is happening is that the 10000 are now being grouped into the 1000 group instead of a new 10000 group being created. See code below.

SELECT
LEFT(table.IDField,1) as PolicyGroup,
CASE LEFT(table.IDField,1)
   WHEN '1' THEN '1000 Group' 
   WHEN '2' THEN '2000 Group' 
   WHEN '3' THEN '3000 Group' 
   WHEN '4' THEN '4000 Group' 
   WHEN '5' THEN '5000 Group' 
   WHEN '6' THEN '6000 Group' 
   WHEN '7' THEN '7000 Group' 
   WHEN '8' THEN '8000 Group' 
   WHEN '9' THEN '9000 Group' 
   WHEN '10' THEN '10000 Group' 
END as PolicyExplanation,
cpsys_Datacurrent.IntegrationId as PolicyNumber2

The ID field is not being padded with anything in front of the number so 4 digits is only 4 digits and 5 is only 5, but I need be able to group by the thousand into and including a 10,000 group.

Upvotes: 0

Views: 116

Answers (2)

shawnt00
shawnt00

Reputation: 17915

An expression like this would deal with the single-digit problem as well as left pad with a leading zero for sorting purposes:

right('0' + cast(cast(IDField as int) / 1000 as varchar(2)) + '000 Group', 11)

Upvotes: 0

GMB
GMB

Reputation: 222482

Why not use arithmetics?

If your database does integer division:

IDField / 1000 * 1000 as PolicyExplanation

If it doesn't:

floor(IDField / 1000) * 1000 as PolicyExplanation

If IDField is a string, then, depending on your database, explicit conversion might be needed:

floor(cast(IDField as int) / 1000) * 1000 as PolicyExplanation

Upvotes: 2

Related Questions