3

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.

Improve this question

3 Answers 3

Reset to default
2

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This is a field, Product1 some_description 1200 GR more description... I only want the integer value. Your query gets first number... I modify it, but with another rows, doesn't match with integer value.
@dankkomcg . . . It is only possible to answer the question that you asked. And in this question, you clearly want the first four digits. If you have another question, then ask it as another question. If you change this question, then you might invalidate this answer and that could draw downvotes.
Its the same, i edit it because its possible that users don't understand. No First four digits, only the First Integer, if you want, formed for four digits. But its the same question.
@dankkomcg . . . This answer should do what you want.
1 
CONVERT(
INT, 
(REPLACE(
    SUBSTRING(
        nome,
        (
            PATINDEX(
                '%[0-9]%',
                REPLACE(
                    REPLACE(
                        nome,
                        '1/2',
                        ''
                    ),
                    '1/4',
                    ''
                )
            )
        ),
        4
    ),
    'G',
    ''
))) as pesoTotal

This resolve the question, thanks.

Improve this answer

Comments

0

Should be something like SUBSTRING(name, PATINDEX('%[0-9][0-9][0-9][0-9][G ][GR]%', name),4), if they always do use the same pattern. Will get some false positives ('1400 Rhinos', '3216GGG' and the like).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.