Insert a dash at the Nth character

[bmacbmac]

Hi.. I'm sure this will be an easy one..

I have a list of numbers
199912345
200012344
200144444

I would like to throw a dash after the 4th character so I have
1999-12345
2000-12344
2001-44444

Can someone help me with this?

Thanks!

 

[AlexCuse]

something like this?

update myTable
set myColumn = cast(left(myColumn, 4) as varchar)
+ '-' + cast(right(myColumn, 5) as varchar)

You will need to change your column's data type to varchar of course. For more information on this stuff I suggest you look up LEFT, RIGHT, SUBSTRING functions in Books Online (SQL Server Help files)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

and of course if this is just for a select:

select cast(left(myColumn, 4) as varchar)
+ '-' + cast(right(myColumn, 5) as varchar)
as ColumnWithDashes
from myTable

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Or...

[tt][blue]
Select Stuff(ColumnName, 5, 0, '-') As ColumnWithDashes
From TableName
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

>>For more information on this stuff
And speaking of stuff....
You could use this.

SELECT STUFF('199912345',5,0,'-')

Thanks George!

Just replace the numbers with your column name.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

HaHa...
George, you beat me to it!
(but I gave you credit for showing me the stuff function)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Oh god.
I can't stop laughing. I think I'm working too many hours.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

It's 10PM George, I know why I'm still at work but why are you still working!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

You guys and your crazy stuff. Thanks for giving me a chuckle this morning!

I will concede, this is perfect time for STUFF. I never seem to think of it 'cause I don't think I've ever used it in any real code...

BMac- you should use Paul or George's suggestions

Ignorance of certain subjects is a great part of wisdom