Remove Data to left or right of a character

[bmacbmac]

Hi I have data in the following format:
1111x222
33x4444
222x1111
4444x33

Is there a script I can run to remove all data to the left of the x? Sometimes I might have to remove to the right of the x too.

Thanks!

Brian

 

[gmmastros]

Take a look here.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server

It's not exactly what you are looking for, but everything you need is explained.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jdemmi]

left of the x

select substr('table.field',1,instr('table.field','x',1)-1) from table

right of the x

select substr('table.field',instr('table.field','x',1)+1,length('table.field')) from table

-- Jason
"It's Just Ones and Zeros

 

[bmacbmac]

That's what I'm talking about. I am using SQL 2000 so I had to make a few adjustments:

For Left:

select substring (table.field,1,charindex('x', table.field,1)-1) from table

For Right:

select substring(table.field,charindex('x', table.field,1)+1,len(table.field)) from table

So far it looks good.