I have a varchar field like:
195500
122222200
I need to change these values to:
1955.00
1222222.00
Add a comment
|
9 Answers
try this
Declare @s varchar(50) = '1234567812333445'
Select Stuff(@s, Len(@s)-1, 0, '.')
--> 12345678123334.45
Comments
Query:
SELECT col,
LEFT(col,len(col)-2) + '.' + RIGHT(col,2) as newcol
FROM Table1
Result:
| COL | NEWCOL |
|-----------|------------|
| 195500 | 1955.00 |
| 122222200 | 1222222.00 |
If you want to add a '.' before the last two digits of your values you can do:
SELECT substring(code,0,len(code)-1)+'.'+substring(code,len(code)-1,len(code))
FROM table1;
Comments
Please try:
select
Col,
REVERSE(STUFF(REVERSE(Col), 1, 2, LEFT(REVERSE(Col), 2)+'.'))
from YourTable
Comments
CREATE TABLE #T ( Value VARCHAR(20) )
INSERT INTO #T ( Value ) VALUES ( 195500 ), ( 122222200)
SELECT
Value
, NewValue = CONVERT(DECIMAL(17,2),CONVERT(DECIMAL,Value) / 100)
FROM #T
| Value | NewValue |
|-----------|------------|
| 195500 | 1955.00 |
| 122222200 | 1222222.00 |
Comments
Please Try : select reverse(stuff(reverse(columnName),3,0,'.') ) from yourTable
Comments
Ran into something similar and came up with this, could probably change this to a function/SP to make it reusable. Scenario faced was inserting a specified character at different positions within the string, for a certain number of times.
/*
--- passed in string or column, N'C4CB6B22250B'
--- desired output, C4:CB:6B:22:25:0D
--- Test values
--- declare @strToChange varchar(50) = N'C4:CB:6B:22:25:0B'
--- declare @strToChange varchar(50) = N'C4CB6B22250B'
*/
declare @strToChange varchar(50) = N'C4CB6B22250B'
IF(SELECT LEN(@strToChange) - LEN(REPLACE(@strToChange, ':', ''))) > 0
BEGIN
---returns count of specified char
SELECT LEN(@strToChange) - LEN(REPLACE(@strToChange, ':', ''))
END
ELSE
BEGIN
declare @charCount int = 5; --# of times to insert the char
declare @shiftPosition int = 2; -- inital insertion shift
While(@charCount > 0)
begin
SET @strToChange = LEFT(@strToChange,len(@strToChange)- @shiftPosition) + ':' + RIGHT(@strToChange,@shiftPosition)
SET @charCount = @charCount - 1 --decrement charCount for each added char
SET @shiftPosition = @shiftPosition + 3 ---increment shift position by 3 for the new char and the chars that were already there
end
SELECT @strToChange
END
Comments
Please see the following code. You can choose the symbols and index in variable.
declare @index int,@sym varchar(10)
set @sym='#'
set @index=2
select left(195500,@index) +''+@sym+''+right(195500,len(195500)-@index)
1 Comment
Arun Singh
You can add symbols on specific positions
declare @a varchar(10) = 'aaa' select concat(@a,'.00')
