CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user9925928
user9925928

Reputation:

How to convert this Values in SQL?

I have values like 

3'023
'464
2'652
21.6%
'64

I am using the following SQL code to change into shown below;

UPDATE table 
SET Adjusted_value_tst = REPLACE(LTRIM(RTRIM(value)), '''', '.')
WHERE [Value] LIKE '%''%';

Results:

3.023
0.464
2.652
21.6%
0.64

This is correct for 3 decimal values. But, I am supposed to convert '64 to 0.064

Please help me how should I modify my query in order to convert values like 3'023 to 3.023 and '64 to 0.064

Thanks Bob

Upvotes: 0

Views: 254

Answers (3)

Lori Kent
Lori Kent

Reputation: 349

Not sure if this helps, but you can build a conversion table of the unique (Distinct) values which need to be converted, then lookup the proper value in your Update statement.

I have exported data to Excel to get a better handle on my data, then run some formula with an output column. Imported it back into Sql and performed an update from the conversion. But whatever you do before changing the data, back up the table.

Upvotes: 0

Thom A
Thom A

Reputation: 95558

Just remove the ' and then divide by 1000:

SELECT *, REPLACE(N,'''','') / 1000.0
FROM (VALUES ('3''023'),('''464'),('2''652'),('''64')) V(N);

If you have to have it to 3 decimal places then use CONVERT to change the value to a decimal(12,3) or similar.

Edit:

It seems the OP doesn't know the syntax for an UPDATE. This would be:

UPDATE YourTable
SET YourColumn = REPLACE(YourColumn,'''','') / 1000.0;

Edit 2:

The OP has more scenarios than were initial described. This should (edit now it does) work, provided there aren't more scenarios we aren't aware of...:

UPDATE YourTable
SET YourColumn = ISNULL(CONVERT(varchar(20),CONVERT(decimal(12,3),TRY_CONVERT(decimal(12,3),REPLACE(YourColumn,'''','')) / 1000)), YourColumn);

This involves several nested CONVERTs. It's not exactly "pretty", however, it is quite simple. The need for the outer CONVERT after the intial TRY_CONVERT is so that the value only has 3 decimal places. A decimal(12/3) / decimal(4,0) would result in a scale of max(6, s1 + p2 + 1) = max(6, 3 + 4 + 1) = max(6,8) = 8, which is more than the OP wants. So the value '3'023' would have ended up as the value '3.02300000'. Precision, scale, and Length (Transact-SQL)

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

If there is always exactly one ' in the data you can locate it with charindex(). From there you can calculate the length of the portions left and right to the '. If the right length is less than 3, you can use stuff() and replicate() to insert as much 0s as needed. If the left portion's length is less than 1, prepend a 0 (else nothing, i.e. an empty string). Then do your replace() as you already did. (Of course you can also do it the other way round, first replace an then do the aforementioned.)

UPDATE elbat
       SET adjusted_value_tst = replace(CASE
                                          WHEN charindex('''',
                                                         rtrim(ltrim(adjusted_value_tst))) < 2 THEN
                                                           '0'
                                                         ELSE
                                                           ''
                                                       END
                                                       +
                                                       stuff(rtrim(ltrim(adjusted_value_tst)),
                                                             charindex('''',
                                                                       rtrim(ltrim(adjusted_value_tst)))
                                                             + 1,
                                                             0,
                                                             replicate('0',
                                                                       3
                                                                       - len(rtrim(ltrim(adjusted_value_tst)))
                                                                       + charindex('''',
                                                                                   rtrim(ltrim(adjusted_value_tst))))),
                                                      '''',
                                                      '.');

SQL Fiddle

Upvotes: 0

Related Questions