Change two part data in a field 1

[Corr]

Hello,

I need to change the following data 'SHELF 123' to look like 'IC-SHELF-00123'

The command I used to conver 'ROW 123' into 'IC-ROW-123' was as follows:

UPDATE <TableName>
SET <FieldName> =
(Select 'IC'+'-'+ RTRIM(Left(<FieldName>,Convert(varchar,PATINDEX ( '% %' , <FieldName>))))+'-'+LTRIM(Right(<FieldName>, Convert(varchar,PATINDEX ( '% %' , <FieldName>)))))

BUT, when I use this same one on SHELF it doesn't work. Then I realized that the number part has to be 5 digits and now I am totally lost.

 

[tlbroadbent]

Try this.

UPDATE <TableName>
SET <FieldName> =
'IC-'+Left(<FieldName>s,charindex(' ',<FieldName>)-1)+'-'+Right(replicate('0',5)+right(<FieldName>,len(<FieldName>)-charindex(' ',<FieldName>)),5) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Corr]

IT WORKED PERFECTLY! Your awsome, thanks.

John