substring help? - 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 2005 Forums

Transact-SQL (2005)

substring help?

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-09-05 : 11:10:45

How can I take the data stripped of all special characters and apply formatting? I was thinking of using substring to apply the formatting to result in: "+1 (xxx) xxx-xxxx".

4134994321
4134994321
4134994321
4134994321
4134994321
8605621150
8605621150
8007671000
8605621150
3152344200

Thanks!

Kristen
Test

22859 Posts

Posted - 2007-09-05 : 11:15:25

Better to do this in the front end application.


SELECT '+1 (' + LEFT(MyColumn, 3) + ') ' + SUBSTRING(MyColumn, 4, 3) + '-' + SUBSTRING(MyColumn, 7, 999)
FROM
(
SELECT [MyColumn] = '4134994321'
UNION ALL SELECT '8605621150'
UNION ALL SELECT '8007671000'
UNION ALL SELECT '3152344200'
) AS X

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 11:16:09

SELECT '+1 (' + SUBSTRING(Col1, 1, 3) + ') ' + SUBSTRING(Col1, 4, 3) + '-' + SUBSTRING(Col1, 7, 4)

But why don't you format the phonenumber at your client application?

E 12°55'05.25"
N 56°04'39.16"

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-09-05 : 11:25:52

Our application formats our phone numbers assuming the user puts in only numerics and no spaces (resulting in +1 (xxx) xxx-xxxx).
Unfortunaly, they can go back in and change the formatting after the fact. As a reult, the data is a mess when viwed as a whole from SQL server. I stripped out all characters and am now left with 10 digit records. I want to apply out default formatting directly to the data, which should look like in +1 (123) 456-7890.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 11:53:01

You mean like in ACCESS? You can't.

You have to separate STORAGE and FORMATTING.
You STORE the 10-digit value in your database, and FORMAT it when displaying it in the application later.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-05 : 12:31:02

As usual, if you want to show data in front end, use Format function there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources