Add dash to data

Question

Post reply

Add dash to data

jordon.shaw

Hall of Fame

Points: 3571
More actions
July 21, 2010 at 8:45 am

#224277

I have an account number field that has an 10 digit account number in it. What I need to do is add dashes in those accounts numbers. For example, if my account number is 1111111111, I want to update that account to say 111-11111-11. What is the best option of doing this?

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

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

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 1

So far, I'm able to select the first three and then add an dash like this:

SELECT LEFT(ACCOUNTNUMBER, 3)+'-'

from BILLING.ACCOUNT

But I'm not sure where to go from here. Should I use a STUFF query?

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Assuming you want the dashes in fixed locations, look up SUBSTRING in BOL

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 3

Thank you! This worked perfectly!

SELECT SUBSTRING(ACCOUNTNUMBER, 1, 3)+'-'+SUBSTRING(ACCOUNTNUMBER, 4, 5)+'-'+SUBSTRING(ACCOUNTNUMBER, 9, 2)

FROM BILLING.ACCOUNT

WayneS SSC Guru Points: 95460 More actions · Answer 4

You might want to look at STUFF also. If you use this, it might be easier to add the dashes from back to front.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

You can also learn about another two string T-SQL functions:

SELECT LEFT(ACCOUNTNUMBER, 3) + '-' + SUBSTRING(ACCOUNTNUMBER, 4,5) + '-' + RIGHT(ACCOUNTNUMBER,2)

FROM BILLING.ACCOUNT

However, it looks like you are going to use this query for output to the client app or report. If that is true, than your best o[ption to do formatting in app or report itself. Using SQLServer to format data for the representation is not the best idea...;-)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

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

I agree with Joe that this should be done in the presentation/application layer. I understand that sometimes this is beyond your control. If you absolutely must accomplish this via TSQL, I recommend the Stuff function, as it is very compact and very clean.

DECLARE @t TABLE(

col VARCHAR(10)

);

INSERT INTO @t VALUES ('1234567890');

INSERT INTO @t VALUES ('1111111111');

SELECT STUFF(STUFF(col,4,0,'-'),10,0,'-')

FROM @t

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Adam Haines (7/21/2010)

I agree with Joe that this should be done in the presentation/application layer. I understand that sometimes this is beyond your control. If you absolutely must accomplish this via TSQL, I recommend the Stuff function, as it is very compact and very clean.

Agreed... sometimes there is no GUI.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)