Robb Hill
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
Thanks that worked fine and sorry for taking so long to get back.
If so, so filter out the row that has that column with less than 6 characters:
Open in new window