question

JS_Tan avatar image
JS_Tan asked

converting a field

Dear Gurus, I have a field with data type – nvarchar(15) and data format read as ABC01500 How do change the format to read as 1.500 I have the field with data type – decimal (5,3) in Table B. The following is the TSQL : INSERT INTO Table B(Field1, Field 2, RATE) SELECT Field1, Field2, CAST(RIGHT(Field3, 4) AS INT)'RATE', (CAST(RATE*0.001)) FROM Table A Thanks.
tsqlconvert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Rajprabu11 avatar image
Rajprabu11 answered
select CAST(SUBSTRING(a, PATINDEX('%[0-9]%', a), PATINDEX('%[0-9][^0-9]%', a + 't') - PATINDEX('%[0-9]%', a) + 1) AS int) * POWER(10.000, -1 * 3) from ( select 'ABC1500' as a union select 'ABC1501' as a union select 'ABC1502' as a)aa where a like '%[0-9]%'
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

please use the above code.. hope this will help you
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.