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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-10 : 09:23:41
|
| Moon writes "Hi,I have a nvarchar field in the database. Sometime user put a space infront of the number which trigges a field. But I need to reformat the data w/o the space for other calcaulation. So my question is: If a filed is stored as " 123455", is there anyway I can reformat as "123455", basically get rid of the space from infront of the sql data?Please let me know.Thanks" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-10 : 09:29:26
|
| If it's only storing numeric values, why not store the column as an int or other numeric datatype? Then you won't have to trim any embedded spaces for calculations.If you want to display it with leading spaces, the STR() function will accommodate you very nicely:SELECT STR(numCol, 7, 0) FROM myTableThis will format the numbers as 7 digits with zero decimal places, padded on the left with spaces. See Books Online for details on STR() and CONVERT(). |
 |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2001-12-10 : 10:32:51
|
| Or you could just reformat the data without the space at the front using the LTRIM function. Something likeUPDATE t1Set f1 = LTRIM(f1)should do the trick. LTRIM removes all leading spaces from a string. There's also an RTRIM to remove trailing spaces.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
|
|
|
deleteting space before data