CODEWITHSUNDEEP
sqlsql-server
Cactus
Cactus

Reputation: 21

SQL Server - Combine string to integer where integer can have a variable number of leading zeros

I have a report in SQL Server Report Builder which brings back the profession acronym (string) and registration number (integer) for each professional in a separate SQL database.

The registration number can be 5 or more digits long, and may start with one or more zeros. For example:

Profession  Registration #
AB          00162
PH          02272
SA          13925
SA          026025
DA          1025927

I'm trying to put the profession acronym and registration number together into a registration ID, because I need to compare this with the registration ID from another (non SQL) database.

I'm trying to get something like this:

Registration ID
AB00162
PH02272
SA13925
SA026025
DA1025927

I've tried converting the integers to strings using the following in my query:

REGISTRY.PROFESSION + right('00000' + cast(REGISTRY.REGISTRATION_NO as varchar(8)), 5) as Full_Reg_Number

However, with the above the integers that are more than 5 digits long get cut off, and if I increase '00000' to, say, '0000000' and the number '5' to '7' in the above, the integers that only have 5 digits are padded with extra leading zeros.

I do not have permission to change the formatting of the integers in either database.

Upvotes: 2

Views: 91

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269993

Based on your problems, I suspect those really are integers. You've just shown them with leading zeros in the question.

A simple solution is to use case:

(REGISTRY.PROFESSION +
 CASE WHEN REGISTRY.REGISTRATION_NO < 10000 THEN right('00000' + cast(REGISTRY.REGISTRATION_NO as varchar(8)), 5) 
      ELSE REGISTRY.REGISTRATION_NO
 END
) as Full_Reg_Number

An even simpler method uses FORMAT():

(REGISTRY.PROFESSION + FORMAT(REGISTRY.REGISTRATION_NO, '00000')
) as Full_Reg_Number

Upvotes: 0

pwilcox
pwilcox

Reputation: 5763

Integers aren't stored with leading zeroes. To be stored like that, then the field is NOT of integer type in the first place. Simply do: 

Registry.profession + registry.registration_no

You can confirm that the stored type is not an integer as follows:

select    data_type
from      information_schema.columns
where     table_name = 'registry'
and       column_name = 'registration_no'

If you're getting a type conversion error as you mention in your comments, then most likely the error is not coming due to this concatenation. It's probably down the line, such as if you're using 'Full_Reg_Number' in a 'where' statement or other comparison that expects a comparison to an integer, and instead is getting a varchar. After all, you called the column 'Full_Reg_Number' even though it's not a number.

Upvotes: 1

Related Questions