Split Text into char and int 1

[robert030975]

HI Guys

I'm struggling at the monment separting txt


WITH t(hours) AS (
Select '3WH' UNION ALL
Select '3WH ' UNION ALL
Select '4WH' UNION ALL
Select '3 WH' UNION ALL
Select '3 ' UNION ALL
Select '4' UNION ALL
Select '3 W' UNION ALL
Select 'WH3 ' UNION ALL
Select 'W 3' )

SELECT PATINDEX('%[0-9]%', hours + '0') FROM t

you can see in the results there is a lot of 1's ideally what I would like to see is (col 1 3, col 2 WH)


any help would be great thanx

 

[simian336]

What is the "hours + '0'" about?

Think this is close.

Simi


WITH t(hours) AS (
Select '3WH' UNION ALL
Select '3WH ' UNION ALL
Select '4WH' UNION ALL
Select '3 WH' UNION ALL
Select '3 ' UNION ALL
Select '4' UNION ALL
Select '3 W' UNION ALL
Select 'WH3 ' UNION ALL
Select 'W 3'
)
select substring(hours,PATINDEX('%[0-9]%', hours),1)
, ltrim(replace(hours,substring(hours,PATINDEX('%[0-9]%', hours),1),'')) from t

 

[robert030975]

Nice I ve never thought of using substring however what happens if the 3 becomes 30 ?

 

[robert030975]

Sorry also

Select '30 ' UNION ALL Select '4.5' UNION ALL

 

[drlex]

I favour a user-defined function

CREATE Function [StripField](@strNum VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9.]%', @strNum) > 0
       BEGIN
          SET @strNum = STUFF(@strNum, PATINDEX('%[^0-9.]%', @strNum), 1, '')
       END
    RETURN @strNum
END

The 'WH' part can then be added on
e.g.
SELECT dbo.stripfield([data]), 'WH' from SOURCE

soi là, soi carré

 

[bborissov]

WITH t(Id, hours) AS (
Select 1, '3WH'   UNION ALL
Select 2, '3WH  ' UNION ALL
Select 3, '4WH'   UNION ALL
Select 4, '3 WH'  UNION ALL
Select 5, '3 '    UNION ALL
Select 6, '4'     UNION ALL
Select 7, '3 W'   UNION ALL
Select 8, 'WH3 '  UNION ALL
Select 9, 'W 3'   UNION ALL
Select 10,'WW'    UNION ALL
Select 11,'W33W'
)
select  CASE WHEN PATINDEX('%[0-9]%', hours) = 0
                  THEN '0'
             WHEN PATINDEX('%[^0-9]%', hours+'W') = 0
                  THEN hours
             ELSE
                SUBSTRING(hours, PATINDEX('%[0-9]%', hours),
                                 PATINDEX('%[0-9][^0-9]%', hours+'W')+1-
                                 PATINDEX('%[0-9]%', hours))
            END AS TestNumber,
        CASE WHEN PATINDEX('%[0-9]%', hours) = 0
                  THEN Hours
             WHEN PATINDEX('%[^0-9]%', hours) = 0
                  THEN ''
             ELSE
             STUFF(hours, PATINDEX('%[0-9]%', hours),
                          PATINDEX('%[0-9][^0-9]%', hours+' ')-
                          PATINDEX('%[0-9]%', hours)+1,'')
             END AS TestString,
                  
            *
 from t

Not tested for every occasions though

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[robert030975]

So Close !!!



WITH t(hours) AS (

Select '3.5ZH' UNION ALL

Select '3WH ' UNION ALL

Select '4WH' UNION ALL

Select '3 WH' UNION ALL

Select '3 ' UNION ALL

Select '4.5' UNION ALL

Select '3 W' UNION ALL

Select 'WH3.5 ' UNION ALL

Select 'W 30' )

select substring(hours,patindex('%[0-9]%',hours),

CASE patindex('%.%',hours)

WHEN 0 THEN patindex('%[0-9]%',hours)

ELSE patindex('%[0-9]%',hours) + 2 END) AS Other

,
CASE WHEN PATINDEX('%[0-9]%', hours) = 0
THEN Hours
WHEN PATINDEX('%[^0-9]%', hours) = 0 THEN '' ELSE
STUFF(hours, PATINDEX('%[0-9]%', hours),
PATINDEX('%[0-9][^0-9]%', hours+' ')-
PATINDEX('%[0-9]%', hours)+1 , '')
END AS TestString ,
hours
FROM t

I just need to try and handle the decimals

 

[robert030975]

Sorry to filter the decimals out on the string!

 

[bborissov]

WITH t(hours) AS (

Select '3.5ZH' UNION ALL

Select '3WH ' UNION ALL

Select '4WH' UNION ALL

Select '3 WH' UNION ALL

Select '3 ' UNION ALL

Select '4.5' UNION ALL

Select '3 W' UNION ALL

Select 'WH3.5 ' UNION ALL

Select 'W 30' ) 

select  CASE WHEN PATINDEX('%[0-9.]%', hours) = 0
                  THEN '0'
             WHEN PATINDEX('%[^0-9]%', hours+'W') = 0
                  THEN hours
             ELSE
                SUBSTRING(hours, PATINDEX('%[0-9]%', hours),
                                 PATINDEX('%[0-9.][^0-9.]%', hours+'W')+1-
                                 PATINDEX('%[0-9]%', hours))
            END AS TestNumber,
        CASE WHEN PATINDEX('%[0-9.]%', hours) = 0
                  THEN Hours
             WHEN PATINDEX('%[^0-9.]%', hours) = 0
                  THEN ''
             ELSE
             STUFF(hours, PATINDEX('%[0-9.]%', hours),
                          PATINDEX('%[0-9.][^0-9.]%', hours+' ')-
                          PATINDEX('%[0-9]%', hours)+1,'')
             END AS TestString,
                  
            *
 from t

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[robert030975]

Def woth a gold star! Thanks