Some character

1

I have word as below :

  1. DUPLEX COATED 400 GR 65 X 78 CM
  2. DUPLEX COATED 250 GR 92.5 X 81 CM-FSC RECYCLED 85%

I need to take the information of those so the result will be :

  1. 400
  2. 250

Does anyone can help?

thx,

2 
-- *** Test Data ***
CREATE TABLE #t
(
    YourCol varchar(255) NOT NULL
);
INSERT INTO #t
VALUES ('DUPLEX COATED 400 GR 65 X 78 CM')
    ,('DUPLEX COATED 250 GR 92.5 X 81 CM-FSC RECYCLED 85%');
-- *** End Test Data ***

SELECT YourCol
    ,SUBSTRING(YourCol, X.StartPos, PATINDEX('%[^0-9]%', SUBSTRING(YourCol, X.StartPos, 255)) - 1) AS Result
FROM #t T
    CROSS APPLY (VALUES (PATINDEX('%[0-9]%', T.YourCol))) X (StartPos);
3

Hi jtaniarto,

This may be a solution to your query

select Substring(Yourcol,PATINDEX('%[0-9]%',Yourcol),CHARINDEX(' ',Yourcol,PATINDEX('%[0-9]%',Yourcol))-PATINDEX('%[0-9]%',Yourcol)) from @t

regards
Anna

4

thanks for the respon..
it tells error said :

Msg 536, Level 16, State 5, Line 3
Invalid length parameter passed to the SUBSTRING function.

thx

5

That's most likely a row with non-matching pattern

Add

WHERE YourCol LIKE '%[0-9]%[^0-9]%'

Note that maybe?? you need to report on the ones which were NOT included in this operation? That would be:

SELECT YourCol, OtherCols
FROM YourTable
WHERE YourCol NOT LIKE '%[0-9]%[^0-9]%' OR YourCol IS NULL
6

sorry...
so what is the complete syntax according the before..so
select Substring(Yourcol,PATINDEX('%[0-9]%',Yourcol),CHARINDEX(' ',Yourcol,PATINDEX('%[0-9]%',Yourcol))-PATINDEX('%[0-9]%',Yourcol)) from @t

and

SELECT YourCol, OtherCols
FROM YourTable
WHERE YourCol NOT LIKE '%[0-9]%[^0-9]%' OR YourCol IS NULL

how can I mix them..

thanks

7

Just add my WHERE to that

8

Tricky. You could use a CASE statement so that the "Result" was only evaluated when the data was appropriate, and you got "N/A" instead on other occasions.

I suggest you just get it working to your satisfactions first, and then add that finesse as Step Two.