CODEWITHSUNDEEP
sqlsql-server
Jeff
Jeff

Reputation: 449

Insert a period before the last two characters of a sql column

Hello I have a column in sql and I need to insert a "." before the last two characters. The column is not fixed length. Can someone help me go about this?

Upvotes: 1

Views: 6522

Answers (4)

Kaf
Kaf

Reputation: 33849

Question is not very clear. If you are trying to insert . before last 2 characters into the column data, you could use STUFF() function.

For ex:

Declare @s varchar(50) = '12345678'
Select Stuff(@s, Len(@s)-1, 0, '.')
--> 123456.78

Applying to your table query:

Select Stuff(yourCol, Len(yourCol)-1, 0, '.')
From yourTable

Please note that If Len(yourCol) is less than 2, a null string will be returned.

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

You can do it with an UPDATE and SUBSTRING.

UPDATE table
SET column = (SELECT SUBSTRING(column, 1, LEN(column)-2) 
              + '.' 
              + SUBSTRING(column, LEN(column)-1, LEN(column))

If you would like to check what the query will do to your column, just use:

SELECT 
    SUBSTRING(column, 1, LEN(column)-2) 
    + '.' 
    + SUBSTRING(column, LEN(column)-1, LEN(column))
FROM table

Upvotes: 5

Eric Hotinger
Eric Hotinger

Reputation: 9316

You can use sp_rename like this:

EXEC sp_rename 'dbo.DatabaseName.ColumnName', 'ColumnNa.me', 'COLUMN';

If you need to apply this to multiple columns, then I would suggest using the substring method. No real use to do it for just one UPDATE on a column.

Upvotes: 1

OGHaza
OGHaza

Reputation: 4795

It looks messy but this should do it:

SELECT LEFT(COL_NAME, LEN(COL_NAME)-1)+'.'+RIGHT(COL_NAME,1)
FROM Table

Or if you want to update the value in the database rather than just the output

Update Table 
SET COL_NAME = LEFT(COL_NAME, LEN(COL_NAME)-1)+'.'+RIGHT(COL_NAME,1)

Upvotes: 1

Related Questions