[Resolved] Remove character and cast

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Remove character and cast

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-18 : 15:48:10

Have following scenario where I need to cast from char(10) to char(9) and remove a character if extsts:

create table	#TempTable
		(fein char(9) default '')


insert into #TempTable (fein) 
SELECT COALESCE(myTable.petroex_fein, ' ') as fein 
From myTable .......

Note: petrox_fein ischar(10) in the table.

If petroex_fein has a dash ('-') in position 3, then it should be removed and shifted 1 position left and then casted to a char(9).

Example 39-1957778 would become 391957778

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-18 : 16:33:27

case when substring(petroex_fein,3,1) = '-' then stuff(petroex_fein,3,1,'') else petroex_fein end
or maybe
coalesce(left(case when substring(petroex_fein,3,1) = '-' then stuff(petroex_fein,3,1,'') else petroex_fein end,9),'')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.