Inserting a dash between some values AND Adding a leading zero if only 5 characters

Question

Inserting a dash between some values AND Adding a leading zero if only 5 characters

C-kweldude

Old Hand

Points: 312
More actions
July 7, 2014 at 7:57 pm

#296285

Hi Guys
I need a bit of help.
I have a column with integer records of 5 and 6 characters.
example below od 2 records.
12345
123456
What I need to do is
1. If its only 5 digits long....insert a leading zero.
2.Once all characters are 6 digits long...insert a dash between them.
So my 2 records above should end up looking like this.
012-345
123-456
Any assistance will be appreciated.
A

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

C-kweldude (7/7/2014)
Hi Guys
I need a bit of help.
I have a column with integer records of 5 and 6 characters.
example below od 2 records.
12345
123456
What I need to do is
1. If its only 5 digits long....insert a leading zero.
2.Once all characters are 6 digits long...insert a dash between them.
So my 2 records above should end up looking like this.
012-345
123-456
Any assistance will be appreciated.
A

Easy, this:

declare @test-2 varchar(10) = '12345';

select stuff(right('000000' + @test-2, 6),4,0,'-')

C-kweldude Old Hand Points: 312 More actions · Answer 2

Hi Lynn

Sorry, still a bit vague for me.

The table name is Branch and the column is called branchcode.

It should be some kind of update statement?

Thanks

A

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

C-kweldude (7/7/2014)
Hi Lynn
Sorry, still a bit vague for me.
The table name is Branch and the column is called branchcode.
It should be some kind of update statement?
Thanks
A

This help any?? By the way, Books Online is your friend and can explain all of this.

http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(STUFF_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(DevLang-TSQL)&rd=true

http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(RIGHT_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(DevLang-TSQL)&rd=true

create table dbo.test(

testid int identity(1,1),

MyValue varchar(10));

insert into dbo.test(MyValue)

values ('12345'),('123456'),('5678'),('56789'),('567890');

select testid, MyValue from dbo.test;

select testid, MyValue, stuff(right('000000' + MyValue, 6),4,0,'-') from dbo.test;

update dbo.test set

MyValue = stuff(right('000000' + MyValue, 6),4,0,'-');

select testid, MyValue from dbo.test;

go

drop table dbo.test;

go

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Also, if you update the values in place, look what happens if you run the STUFF(RIGHT...)) against the modified values:

create table dbo.test(

testid int identity(1,1),

MyValue varchar(10));

insert into dbo.test(MyValue)

values ('12345'),('123456'),('5678'),('56789'),('567890');

select testid, MyValue from dbo.test;

select testid, MyValue, stuff(right('000000' + MyValue, 6),4,0,'-') from dbo.test;

update dbo.test set

MyValue = stuff(right('000000' + MyValue, 6),4,0,'-');

select testid, MyValue from dbo.test;

select testid, MyValue, stuff(right('000000' + MyValue, 6),4,0,'-') from dbo.test;

go

drop table dbo.test;

go

C-kweldude Old Hand Points: 312 More actions · Answer 5

Hi Lynn

Awesome!:-)

Cheers for the help and the links...ill check them out.

A

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

C-kweldude (7/7/2014)
...I have a column with integer records of 5 and 6 characters....

What datatype is the column? It's not clear if you are describing the datatype or the content.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MMartin1 One Orange Chip Points: 27799 More actions · Answer 7

ChrisM makes a good point as a numeric column will not give the expected results...

Either the column is a char/varchar or will have to be cast at select time because if we take

select '0'+12

it will upconvert to '0' to 0 and add it to 1.

Thus if the column is numeric...

(If I can borrow a piece from Lynn's code) >

select testid, MyValue, stuff(right('000000' + CAST(MyValue as varchar(10)), 6),4,0,'-') from dbo.test

should work.

----------------------------------------------------

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

MMartin1 (10/8/2014)
ChrisM makes a good point as a numeric column will not give the expected results...
Either the column is a char/varchar or will have to be cast at select time because if we take
select '0'+12
it will upconvert to '0' to 0 and add it to 1.
Thus if the column is numeric...
(If I can borrow a piece from Lynn's code) >
select testid, MyValue, stuff(right('000000' + CAST(MyValue as varchar(10)), 6),4,0,'-') from dbo.test
should work.

This would be even better for integers:

select testid, MyValue, stuff(right(1000000 + MyValue, 6),4,0,'-')

from dbo.test

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2