CODEWITHSUNDEEP
sqlsql-server
user3463810
user3463810

Reputation: 13

MS-SQL - Extracting numerical portion of a string


I have an MS-SQL table, with a column titled 'ImportCount'.
Data in this column follows the below format:

ImportCount
[Schedules] 1376 schedule items imported from location H:\FOLDERA\AA\XX...
[Schedules] 10201 schedule items imported from location H:\FOLDERZZ\PERS\YY...
[Schedules] 999 schedule items imported from location R:\PERS\FOLDERA\AA\XX...
[Schedules] 21 schedule items imported from location H:\FOLDERA\MM\2014ZZ...


What I would like to do is extract that numerical portion of the data (which varies in length), but am struggling to get the right result. Would appreciate any help on this! Thanks.

Upvotes: 1

Views: 67

Answers (4)

TechDo
TechDo

Reputation: 18629

Please try:

SELECT LEFT(Val,PATINDEX('%[^0-9]%', Val+'a')-1) from(
    SELECT
       STUFF(ImportCount, 1, PATINDEX('%[0-9]%', ImportCount)-1, '') Val
    FROM YourTable
)x

Upvotes: 0

Arjit
Arjit

Reputation: 3456

Try this..You can declare it as a SQL function also.

DECLARE @intText INT
DECLARE @textAplhaNumeric varchar(100)

set @textAplhaNumeric = '1376 schedule items imported from location'
SET @intText = PATINDEX('%[^0-9]%', @textAplhaNumeric)

BEGIN
WHILE @intText > 0
BEGIN
SET @textAplhaNumeric = STUFF(@textAplhaNumeric, @intText, 1, '' )
SET @intText = PATINDEX('%[^0-9]%', @textAplhaNumeric)
END
END

Select @textAplhaNumeric //output is 1376

It will work in case of NULL or empty values.

Upvotes: 0

Habeeb
Habeeb

Reputation: 1040

select SUBSTRING(ImportCount,13,patindex('% schedule items%',ImportCount)-13) from table name

Upvotes: 0

MusicLovingIndianGirl
MusicLovingIndianGirl

Reputation: 5947

Try

select left(ImportCount, patindex('%[^0-9]%', ImportCount+'.') - 1)

Upvotes: 1

Related Questions