Concatenating Text to Numeric Data

Question

Post reply

Concatenating Text to Numeric Data

dmessmer

SSC Veteran

Points: 222
More actions
July 13, 2010 at 11:00 am

#143996

I am writing a query to create a text file for our school district's food service software from our student information system. I need to pad the Student ID number (ID) with four zeros (long story, vendor related). The Student ID number is an integer data type.
It works great in MS Access with the following statement
SELECT "0000" & [dbo_STU]![ID] AS StuID
FROM dbo_STU;
but I would prefer to do it on our SQL Server.
When I use the following statement
SELECT '0000' + ID AS StuID
FROM STU
I get the ID but no padding as I assume, it is just doing the math. If I substitute ID for FN for example which is a text field, it works great.
Is there some way to concatenate the text and numeric data in a single field on the fly?
Thanks.
Dave

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

If you are just using the SELECT for a report, can you just do this:

SELECT '0000' + CAST(ID AS VARCHAR) AS StuID

FROM STU

?

_________________________________
seth delconte
http://sqlkeys.com

Kit G SSCrazy Points: 2888 More actions · Answer 2

In order to do the padding, you have to convert your INT field to a varchar or some other field that allows for alpha-numeric data. To SQL, '01' and '1' are not the same.

Just out of curiosity, when your student ID increments another digit (i.e., goes from 9999 to 10000), does the vendor program still require 4 leading zeros? Or is it that the vendor program is requiring an ID to be of a specific character length, hence the padding zeros? If it is the later, then use of RIGHT commands will be necessary. If you just need four leading zeros, the first examle should do you just fine. If you need a specific string length, then use the second example that will give you the same string length no matter the length of the field having the zeros added.

--Creates an ID with 4 leading zeros

SELECT '0000' + CAST(StudentID AS VARCHAR(10)) AS StuID

FROM dbo.STU

--Creates an 8 character long string no matter the string length of the ID

SELECT RIGHT('0000000' + CAST(StudentID AS VARCHAR(10)), 8) AS StuID

FROM dbo.STU

If you want to store this ID number in your database, you're going to need to store it as a VARCHAR (or other alpha-numberic data type) since INT will stirp off the leading zeros.

-- Kit

dmessmer SSC Veteran Points: 222 More actions · Answer 3

Thanks. I'd found the CAST statement in my searches but was having trouble working out the syntax.

Just out of curiosity, when your student ID increments another digit (i.e., goes from 9999 to 10000), does the vendor program still require 4 leading zeros? Or is it that the vendor program is requiring an ID to be of a specific character length, hence the padding zeros?

Actually they are requiring it to be of a specific character length. We have IDs from 1 digit to 5. I already worked out the RIGHT command but was having trouble getting the concatenation to work.

With our old system, when I did the queries in Access I used nested If statements with the LEN property to add 0000 if Len was 1, 000 if Len was 2 etc.

Thanks again for the help.

Dave

Adam Haines SSC-Insane Points: 23217 More actions · Answer 4

This is a pretty straight forward request. You only need to use the right function to pad the string.

DECLARE @t TABLE(Id INT);

INSERT INTO @t VALUES(1);

INSERT INTO @t VALUES(10);

INSERT INTO @t VALUES(100);

INSERT INTO @t VALUES(1000);

INSERT INTO @t VALUES(10000);

SELECT RIGHT('00000' + CAST(ID AS VARCHAR(10)),5) as Id

FROM @t

/*

Id

-----

00001

00010

00100

01000

10000

*/

My blog: http://jahaines.blogspot.com

Kit G SSCrazy Points: 2888 More actions · Answer 5

dmessmer (7/13/2010)
Thanks. I'd found the CAST statement in my searches but was having trouble working out the syntax.

I find myself looking up CAST and CONVERT a lot of time. CAST is basically just changing the data type. You can use the chart in Books OnLine to figure out if the change will work.

CAST(StudentID as VARCHAR(10)) is just telling SQL to change whatever data type StudentID is into the VARCHAR data type with a length of 10 characters. When I'm converting data types into VARCHARs, I like to have a few extra characters available so I can make sure I'm getting what I should be getting and not truncating data. It also leaves a bit of room for growth.

Actually they are requiring it to be of a specific character length. We have IDs from 1 digit to 5. I already worked out the RIGHT command but was having trouble getting the concatenation to work.
With our old system, when I did the queries in Access I used nested If statements with the LEN property to add 0000 if Len was 1, 000 if Len was 2 etc.

Okay, so you need a character string that is 5 characters long, right?

SELECT RIGHT('0000' + CAST(StudentID as varchar(10)), 5) AS StuID

FROM dbo.StuID

This will take your StudentID, change it to a VARCHAR data type and append four leading zeros to it. The RIGHT command tell SQL to only take 5 characters from the RIGHT.

So when you have '0000' plus '12345' you will get '000012345', but the RIGHT command will only return '12345'.

SELECT '0000' + CAST(12345 AS VARCHAR(10))

-- gives you '000012345'

SELECT RIGHT('00001234', 5)

-- gives you '01234'

Hope that clears some stuff up for you. 🙂

-- Kit

dmessmer SSC Veteran Points: 222 More actions · Answer 6

Kit G (7/13/2010)
Hope that clears some stuff up for you. 🙂

Yes it does.

Thank you very much.

Dave

Kit G SSCrazy Points: 2888 More actions · Answer 7

Kit G

SSCrazy

Points: 2888

July 13, 2010 at 2:30 pm

#1192343

You're welcome.

-- Kit