How Do I strip a set of numbers from a VarChar Column

[jabmod]

I have a column that is 8 characters in lenght.

The values in starts with:
1) a set of numbers varies in lenght
2) next is 2 spaces
3) then a set of characters that varies in lenght starting with P and tries to spell the word Point.

Examples are:
699 Poi
1,314 P
90 Poin
0 Point

I would like to pull only the numbers from this column so I can now have
699
1,314
90
0

Thanks for your help

 

[gmmastros]

Since it will always be a number followed by a space...

Select Left(Field, CharIndex(' ', Field)-1)
From   SomeTable

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

SELECT LEFT(MyField, CHARINDEX(MyField,' ')-1) AS MyField
       FROM MyTable

This will works ONLY if first you didn't have a space between digits.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[SQLDenis]

left(col,patindex('% %',col))

example

select left(col,patindex('%  %',col)) from (
select '699  Poi' as Col union all
select '1,314  P' union all
select '90  Poin' union all
select '0  Point' ) x

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/