format a string - 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 2000 Forums

Transact-SQL (2000)

format a string

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-05-19 : 03:43:41

Is there a way to format strings as in the Format function in VB, Access etc?

I have looked at the String functions but don't see one?

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 05:58:24

i think there's no format function in SQL... you have to do your personal formatting bro..
convert + settings can be...

Cursors are for those who doesn't know how to use SQL

raclede™

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-19 : 06:35:48

What kind of Format function do you mean?
If you mean, Date format, you have to use convert function
eg
select convert(varchar(25),getdate(),113)

Madhivanan

Failing to plan is Planning to fail

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-05-19 : 06:50:43

I mean like as in VB format(3.123456, "#.##) results in 3.12

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 07:03:39

hey bro this one answers your question:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Cursors are for those who doesn't know how to use SQL

raclede™

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-19 : 07:14:23

Hog
Tip: Leave formatting to your presentation layer unless its absolutely necessary

Andy

Beauty is in the eyes of the beerholder

osbertv
Starting Member

6 Posts

Posted - 2005-07-03 : 04:01:51

hog,
i have the same problem formatting string to like "#,###.00".
do you already have the solution to this.
and ya its for presentation layer.

osbertv

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-04 : 22:00:05

create a function in your presentation layer

but if you're really up to it, use convert or case

--------------------
keeping it simple...

Dragonbite
Starting Member

1 Post

Posted - 2005-11-28 : 18:29:45

What can you do if you want to format a phone number such as
8889991000 to the format of 888-999-1000. I haven't found an easy way to do this especially if the phone number can be entered in multiple ways such as
8889991000
(888) 999-1000
888-999-1000
etc.
No matter how it is entered and stored I need it to come out in Reporting services as 888-999-1000

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-11-28 : 23:59:14

What I often do is to reformat it where its entered and then reflect it back to the user for confirmation, then store it in that format...eg
[user types] 8889991000
[on update reformat to] 888-999-1000
[wait for user to confirm]
[store as varchar]

(eg if on a web page, use javascript to update the value before they press submit)

but you're right there's no easy answer...

The benefit of reformatting and reflecting it back to the user before storage is that it gives the user the opportunity to try again if the reformatting results in rubbish. For example, here in Oz, area codes at the front of numbers are optional unless you're in another area code. But you also can't really tell straight away whether the number is a number with an area code or some special type of number like a mobile. So you don't really know for example whether 0287879393 is really (02) 8787 9393 (ie with area code) or 0287 878 933 (mobile format).

So if you reformat and then reflect, you give the user the opportunity to catch an obvious reformatting problem BEFORE it becomes a problem.

(OK I can already hear the moans of "you aussies are crazy")

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ksnkarthik
Starting Member

1 Post

Posted - 2009-03-27 : 17:54:28

In order to format the phone no

[user types] 8889991000
[on update reformat to] 888-999-1000

the below procedure can be used for formatting the phone no

CREATE FUNCTION DBO.PFM_REFORMAT_PHONE
(
@P_NO NVARCHAR(20)
)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @FORMAT_PHONE_NO NVARCHAR(50)
DECLARE @PH_NO NVARCHAR(50)

SET @PH_NO = @P_NO
IF LEN(@PH_NO) > 10
SELECT @FORMAT_PHONE_NO = SUBSTRING(@PH_NO,1,3) + '-' + SUBSTRING(@PH_NO,4,3)+ '-' + SUBSTRING(@PH_NO,7,4) + '-' + SUBSTRING(@PH_NO,11,10)
ELSE IF LEN(@PH_NO) >= 7 AND LEN(@PH_NO) <= 10
SELECT @FORMAT_PHONE_NO = SUBSTRING(@PH_NO,1,3) + '-' + SUBSTRING(@PH_NO,4,3)+ '-' + SUBSTRING(@PH_NO,7,4)
ELSE IF LEN(@PH_NO) >= 4 AND LEN(@PH_NO) < 7
SELECT @FORMAT_PHONE_NO = SUBSTRING(@PH_NO,1,3) + '-' + SUBSTRING(@PH_NO,4,3)
ELSE IF LEN(@PH_NO) <= 3
SELECT @FORMAT_PHONE_NO = SUBSTRING(@PH_NO,1,3)

RETURN @FORMAT_PHONE_NO
END

I use this for function in my query to format it. If ther is any other solution kindly post it.

Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-27 : 20:13:42

Mr.Karthik..please dont resurrect old threads..if you have something to ask or share..open a new thread.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources