CODEWITHSUNDEEP
sqlsql-server
Cookies
Cookies

Reputation: 127

How to convert or cast int to string in SQL Server

Looking at a column that holds last 4 of someone's SSN and the column was originally created as an int datatype. Now SSN that begin with 0 get registered as 0 on the database.

How can I convert the column and it's information from an int into a string for future proof?

Upvotes: 4

Views: 49393

Answers (7)

Bill Brutzman
Bill Brutzman

Reputation: 21

declare @today dateTime = getDate() declare @today_F varChar(11) = convert(varChar(11), @today, 101)

declare @this_year varChar(4) = datePart(yy, @today) declare @Jan_01 varChar(11) = '01/01/' + @this_year

declare @Julian int = dateDiff(dd, @Jan_01, @today_F) declare @Julian_Plus int = @Julian + 1000

declare @Julian_Plus_F varChar(4) set @Julian_Plus_F = convert( varChar(4), @Julian_Plus )

declare @Julian_F varChar(3) set @Julian_F = right(@Julian_Plus_F, 3)

Upvotes: 0

jp2code
jp2code

Reputation: 188

There are lots of STR functions in the SQL that I deal with.

To convert nEmpID INT to a string:

SET @value = 'nEmpID: ' + STR(nEmpID);

REF: https://www.w3schools.com/sql/func_sqlserver_str.asp

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

If you specifically want zero-padded numbers, then the simplest solution is format():

select format(123, '0000')

If you want to fix the table, then do:

alter table t alter column ssn4 char(4);  -- there are always four digits

Then update the value to get the leading zeros:

update t
    ssn4 = format(convert(int, ssn4), '0000');

Or, if you just want downstream users to have the string, you can use a computed column:

alter table t
    add ssn4_str as (format(ssn4, '0000'));

Upvotes: 2

mkRabbani
mkRabbani

Reputation: 16908

If you are looking for converting values in the column for your purpose to use in application, you can use this following-

SELECT CAST(your_column AS VARCHAR(100))
--VARCHAR length based on your data

But if you are looking for change data type of your database column directly, you can try this-

ALTER TABLE TableName 
ALTER COLUMN your_column VARCHAR(200) NULL
--NULL or NOT NULL based on the data already stored in database

Upvotes: 3

BillRuhl
BillRuhl

Reputation: 395

First thing never store SSN or Zip Code as any numeric type. Second you should fix the underlying table structure not rely on a conversion...but if you're in a jam this is an example of a case statement that will help you.

IF OBJECT_ID('tempdb..#t') IS NOT NULL
    BEGIN
        DROP TABLE #t
    END
GO
CREATE TABLE #t(
    LastFourSSN INT
)

INSERT INTO #t(LastFourSSN)
VALUES('0123'),('1234')

SELECT LastFourSSN --strips leading zero
FROM #t

SELECT -- adds leading zero to anything less than four charaters
    CASE 
        WHEN LEN(LastFourSSN) < 4 
        THEN '0' + CAST(LastFourSSN AS VARCHAR(3)) 
        ELSE CAST(LastFourSSN AS VARCHAR(4)) 
    END LastFourSSN 
FROM #t

Upvotes: 0

Piotr Palka
Piotr Palka

Reputation: 3159

If you want to add leading zeros, use:

SELECT RIGHT('0000'+ISNULL(SSN,''),4)

Upvotes: 0

Carlos Alves Jorge
Carlos Alves Jorge

Reputation: 1985

You should convert. CONVERT(VARCHAR(4), your_col)

Upvotes: 7

Related Questions