Link to home
Start Free TrialLog in
Avatar of Robb Hill
Robb HillFlag for United States of America

asked on

SQL - format decimal in a string

I am trying to get this to work...this should be simple but I am getting some data errors.

I have a string of numbers.

The numbers can be any length.

I need to insert a decimal after the 5th number counting from the left.

I was able to do this fairly easy with this:

stuff(@names,6,0,' ,')

But on strings that were exactly 5 digits it made the result a null.

So in this conversion I need the following rule to happen.

Place a decimal only if the length is greater than 5.  I would not want a decimal if there were only 5 digits.

So the data would look like this after the convert.  Please provide an efficient method.  I am doing this convert at the select level of the query.

9432
99995
99995.6
99995.72332

etc.
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think you're using an UPDATE statement, right?
If so, so filter out the row that has that column with less than 6 characters:
UPDATE TableName
SET names = stuff(@names,6,0,' ,')
WHERE LEN(names)>5

Open in new window

Avatar of Robb Hill

ASKER

it would be a select...so is stuff the most efficient method?
Do you see any bad performance with it? If not, then use STUFF. I guess any other solution will be more complex to implement.
Thanks that worked fine and sorry for taking so long to get back.