sql server 2008 - sql trim and replace -

can trim , replace @ same time column's name can break down in 2 parts? expected result in namel , namer

name        namel    namer ab_1x       ab       1x axg_3x      axg      3x 4g_12x      4g       12x 

tried using query:

select *, right(name, len(name)-4) namer, left(name, len(name)-4) namel mytable 

the problem trims this:

namel     ab_ axg 4g_ 

same namer, because i'm taking fixed size. how can eliminate "_" , achieve expected outcome? thank in advance

try combination of substring , charindex functions:

declare @t table(name nvarchar(10))  insert @t values ('ab_1x'), ('axg_3x'), ('4g_12x')  select name,         substring(name, 1, charindex('_', name) - 1) namel,        substring(name, charindex('_', name) + 1, len(name)) namer @t 

i assume there underscore symbol in name column.