Remove string chars and keep number trim

[timscronin]

I have a SQL table which has field data like below

field a field b
123 KB 246 kb

How can I strip out just the numbers(there will always be a space between the number and KB?
Thanks

 

[tlbroadbent]

Here are some options.

Select
Left(Field1, charindex(' ', Field1)-1) As Field1,
Left(Field2, charindex(' ', Field2)-1) As Field2
From Table

If the trailing characters are always ' kb' you can use the Replace function in SQL 7 and above.

Select
Replace(Field1, ' kb', '') As Field1,
Replace(Field2, ' kb', '') As Field2
From Table
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[timscronin]

Thanks