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.
| Author |
Topic |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2005-07-14 : 03:59:44
|
| Hi,There is a phone field ini the table stored as varchar(50).Some of the values do have spaces between the numbers (i.e. properly formatted. 020 7345 5432)But others may be just numbers without spaces (i.e. 02086543987)How can I have a format for this phone number please?Thanks |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-07-14 : 04:02:31
|
you can use stuff to insert a space between the numbers since the data is fixed lengthfrom BOLquote:
STUFFDeletes a specified length of characters and inserts another set of characters at a specified starting point.SyntaxSTUFF ( character_expression , start , length , character_expression ) Argumentscharacter_expressionIs an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.startIs an integer value that specifies the location to begin deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned.lengthIs an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression.Return TypesReturns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.
--------------------keeping it simple... |
 |
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-07-14 : 04:05:09
|
| so how do you format 02071234567 to 020 7123 4567Thanks |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-07-14 : 04:31:11
|
| worked it out. thank for the help |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-07-14 : 04:35:23
|
| It is better to do this formation in Presentation layerMadhivananFailing to plan is Planning to fail |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-07-14 : 08:33:45
|
quote:
you can use stuff to insert a space between the numbers since the data is fixed length
varchar(50) is fixed length now?  and mad is right, all formatting should be done in the presentation layer. Let SQL store the data.*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
|
|
|
|
format