问题:

I want to extract a specific number value from this query...

Column: name Four rows for this example:

1/2 Product1 some_description 1200 GR more data... (UND)
1/4 Product2 some_description 2400G more data (KG more data)
Product3 some_description 2200 GRS more data...
1/4 Product4 some_description 1800GR more data UND...

I want the integer value only. I want with the query:

1200
2400
2200
1800

The patterns are:

[0-9]{4} G
[0-9]{4}GR
[0-9]{4} GRS

How can i use this regexp on a SQL Query to parse an attribute value?

SELECT FROM myTable SUBSTRING(name, (PATINDEX('%[0-9]%', [name])),4) as peso

This extract some values, but not in correct order... I think that i can apply LEFT with length until integer value, but i don't know how resolve it.

回答1:

You are close. In SQL Server, the simplest method for your data is:

select left(name, 4) as peso
from mytable t;

That is, the first four characters seem to be what you want.

If the first four characters may not all be digits, then you want to use patindex():

select left(name, patindex('%[^0-9]%', name + ' ') - 1) as peso
from myTable t;

回答2:

CONVERT(
INT, 
(REPLACE(
    SUBSTRING(
        nome,
        (
            PATINDEX(
                '%[0-9]%',
                REPLACE(
                    REPLACE(
                        nome,
                        '1/2',
                        ''
                    ),
                    '1/4',
                    ''
                )
            )
        ),
        4
    ),
    'G',
    ''
))) as pesoTotal

This resolve the question, thanks.