substring - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

substring

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2011-06-21 : 09:09:10

I have field called invoicenum which is numeric,but i need
to display like 0000001
example if invoiceno 1 i need to display DTS0001,DTS0100, give me valuable suggestions.

Desikankannan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-21 : 09:28:35

Many people on this forum would advise you (for some very good reasons) to do that type of formatting on the client side - for example in your GUI/Middle-tier rather than in database. However, if you do want to do it in the database:


select right('0000000'+cast(invoiceno as varchar(32)),7)

or if you want DTS prefix:

select 'DTS'+right('0000000'+cast(invoiceno as varchar(32)),7)

desikankannan
Posting Yak Master

152 Posts

Posted - 2011-06-21 : 09:54:04

quote:
Originally posted by sunitabeck

Many people on this forum would advise you (for some very good reasons) to do that type of formatting on the client side - for example in your GUI/Middle-tier rather than in database. However, if you do want to do it in the database:

select right('0000000'+cast(invoiceno as varchar(32)),7)
or if you want DTS prefix:
select 'DTS'+right('0000000'+cast(invoiceno as varchar(32)),7)

Hi try your code
below

select 'DTS'+right('000'+cast(invoice.dinvoiceno as varchar(32)),7) from invoice

its coming wrong, for example my invoiceno is 123 , it should come
like DTS0123 , BUT ITS COMING LIKE DTS000123

Desikankannan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-21 : 10:08:04

Change the number of zeros and the 7 to however many digits you would like to have. For four digits


select 'DTS'+right('0000'+cast(invoice.dinvoiceno as varchar(32)),4) from invoice

desikankannan
Posting Yak Master

152 Posts

Posted - 2011-06-21 : 10:18:44

quote:
Originally posted by sunitabeck

Many people on this forum would advise you (for some very good reasons) to do that type of formatting on the client side - for example in your GUI/Middle-tier rather than in database. However, if you do want to do it in the database:

select right('0000000'+cast(invoiceno as varchar(32)),7)
or if you want DTS prefix:
select 'DTS'+right('0000000'+cast(invoiceno as varchar(32)),7)

thanks its working

Desikankannan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources