[RESOLVED] Parsing NUmbers from SAtrings in SQL

[FishGuy]

Hi,

I have a column in a table which holds strings like below
What would be the best way in a select query to get the two numeric values as integers on their own so I could use them for display in seperate fields and joins on other tables?

Employee updated - Prev EmployeeNumber 4107964 - New EmployeeNumber 4107975

[GaryMazzone]

What database?

[GaryMazzone]

This might be a start for SQL Server:

DECLARE @test VARCHAR(100) ,
        @temp VARCHAR(100)
DECLARE @Start INT ,
        @End INT ,
        @Len INT        
SET @test = 'Employee updated - Prev EmployeeNumber 4107964 - New EmployeeNumber 4107975'
SELECT  @Len = LEN(@test), @Start=1
SELECT @Len
    WHILE  @Start > 0 
        BEGIN 
            SET @Start= CHARINDEX('-',@test,@Start)
            SET @End = CHARINDEX('-',@test,@start + 1)
            SELECT @start,@end-@start
            IF @End > 0
                BEGIN
                    SELECT SUBSTRING(@test,@start,@end-@start)                  
                END
            ELSE 
                BEGIN
                    SELECT SUBSTRING(@test,@start,@Len + 1 -@start)     
                END
            SET @Start = @End 
        END

result of the above is:
- Prev EmployeeNumber 4107964
- New EmployeeNumber 4107975