SQL Server - T-SQL Statement

Asked By madhavi on 01-Aug-11 08:39 PM

Hi,

      You are a T-Sql developer who needs to cleanup bad data from the source systems.u need to update the entire Employee table.The I.C. number column is called ICNumber.

    You need to change ICNumber from xxxxxxxxxxxxxxx to this format xxx-xx-xxxx

    Write T-Sql ststement to accomplish this task.

Thanks in advance.Please help me.

Peter Bromberg replied to madhavi on 01-Aug-11 09:23 PM

USE SUBSTRING ( value_expression , start_expression , length_expression )

UPDATE TABLENAME SET ICNJUMBER = SUBSTRING(ICNUMBER,1,3) +'-' + SUBSTRING(ICNUMBER,4,2) + '-' + SUBSTRING(ICNUMBER,6,4)

Riley K replied to madhavi on 01-Aug-11 10:05 PM

Use SubString function for your requirement

select case when len(ltrim(rtrim(User_Phone)))='10' then '('+SUBSTRING(User_Phone,1,3)+')'+' '+SUBSTRING(User_Phone,4,3)+'-'+SUBSTRING(User_Phone,7,4) else User_Phone end AS User_Phone from users

Try this and let me know

Web Star replied to madhavi on 01-Aug-11 11:46 PM

you can use substring function to get those chars which needed from existing string and than append your char '-' as follows

Declare @ICNumber varchar(20)

Set @ICNumber = 'xxxxxxxxxxx'

Select Substring(@ICNumber , 1,3) -- Return first xxx

Select Substring(@ICNumber , 4,2) -- Return 2nd xx

Select Substring(@ICNumber , 7,4) -- Return 3rd xxxx

Select Substring(@ICNumber , 1,3) + '-' + Substring(@ICNumber , 4,2) + '-' + Substring(@ICNumber , 7,4)