getting numbers out of a string

[dfwcharles]

I have some character strings that look like this:

PM-FV-CARS-TRUCKS-5k
PM-FV-CARS-TRUCKS-10k
PM-FV-CARS-TRUCKS-100k

I would like to get just the number out of the string.
i tried using this:

declare @jpnum varchar(25)
set @jpnum = 'PM-FV-CARS-TRUCkS-100k'
select substring(@jpnum,19,(len(@jpnum)-len(right(@jpnum,charindex('k',@jpnum)-1))))

but it returns the number plus the K eg; "5K", "100K"
I need it without the K.

Any ideas?
Thanks

 

[mwolf00]

declare @jpnum varchar(25)
set @jpnum = 'PM-FV-CARS-TRUCkS-100k'
select LEFT(substring(@jpnum,19,(len(@jpnum)-len(right(@jpnum,charindex('k',@jpnum)-1)))), LEN(substring(@jpnum,19,(len(@jpnum)-len(right(@jpnum,charindex('k',@jpnum)-1))))-1)



Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mwolf00]

Think I shorted you a parenthesis...

declare @jpnum varchar(25)
set @jpnum = 'PM-FV-CARS-TRUCkS-100k'
select LEFT(substring(@jpnum,19,(len(@jpnum)-len(right(@jpnum,charindex('k',@jpnum)-1)))),len(substring(@jpnum,19,(len(@jpnum)-len(right(@jpnum,charindex('k',@jpnum)-1)))))-1)

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[dfwcharles]

Works great! Thanks!

 

[Hansje]

Had the same problem a week ago, this is was suggested, and it's working fine:

CREATE TABLE Sometable (currencyvalues VARCHAR(30) PRIMARY KEY)

INSERT INTO Sometable VALUES ('PM-FV-CARS-TRUCKS-5k')
INSERT INTO Sometable VALUES ('M-FV-CARS-TRUCKS-10k')
INSERT INTO Sometable VALUES ('PM-FV-CARS-TRUCKS-100k')

SELECT SUBSTRING(currencyvalues,
PATINDEX('%[0-9]%',currencyvalues),
PATINDEX('%[^0-9.]%',SUBSTRING(currencyvalues,
PATINDEX('%[0-9]%',currencyvalues),20)+' ')-1)


HTH,
Hans Brouwer
FROM Sometable