CODEWITHSUNDEEP
sql
user1389971
user1389971

Reputation: 107

Leading Zero's + concatanation in SQL

I have a drop down box in InfoPath that I am trying to populate with info from two fields in an SQL table but seem to be having trouble with getting it exactly the way I want.

One field is an int field with up to 4 digits (0 - 1956), the other field is a name field. Ideally I would like to concatenate the two into the one drop down box separated by two tabs, sorting by the number and adding the leading zero's where appropriate (so 12 instead reads as 0012), but I'm not sure how the SQL statement on that would be.

I have the concatenation down using the below statement, though char(9) doesn't actually insert a tab between the two fields (could be an InfoPath thing? Or is that wrong?)

SELECT CONVERT(varchar(5), myNumber) + char(9) + char(9) + RTRIM(Name) 
    AS myField 
FROM myTable order by myField

Since the leading zeroes are not there, it gets sorted by 0, 1, 10, 100, 1000, 1001 etc. I've seen an example of someone putting the leading zeros at this link http://geekswithblogs.net/nagendraprasad/archive/2009/03/19/formatting-number-to-add-leading-zeros---sql-server.aspx but I am unsure how to combine the two so that concatanation is happening in the one call. Anyone wish to help me with this?

Upvotes: 0

Views: 136

Answers (1)

George Mastros
George Mastros

Reputation: 24498

SELECT Right('00000' + CONVERT(varchar(5), myNumber), 4) + char(9) + char(9) + RTRIM(Name) 
    AS myField 
FROM myTable order by myField

Upvotes: 1

Related Questions