Converting Data | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Converting Data

I am trying to convert certain characters to numbers, but the character value is the last position in my field. How do I do that? I tried the following SQL Transact code, but it didn’t like the substring and when I take that off it clears my whole field out. What would be my best method in SQL Transact to perform my evaluation on a 9 position field with position 9 holding a character instead of a number? (Below is my code, but it doesn’t like the substring) update inputrxfile
set substring(cost,124,1) = case
When ‘{‘ Then ‘0’
When ‘}’ Then ‘0’
When ‘A’ Then ‘1’
When ‘J’ Then ‘1’
else ‘ ‘
end ;

Can you use somethign like this set substring(cost,124,1) = case
When ‘{‘ Then RIGHT (cost,124,123)+ ‘0’

end ; —————————————-
http://spaces.msn.com/members/dineshasanka

Try this update inputrxfile
set cost =substring(cost,1,123) + case substring(cost,124,1)
When ‘{‘ Then ‘0’
When ‘}’ Then ‘0’
When ‘A’ Then ‘1’
When ‘J’ Then ‘1’
else ‘ ‘
end ;
Madhivanan Failing to plan is Planning to fail
]]>