Extract first number value from string sql

Question

I have found many answers that have pointed me on the right track for what I want, like this.

However, if I had a string like "this attachment will be 22 in. x 15 in. long" I would want to grab "22" (the first int value in the string). Also, I won't know how large (# of characters) the int value will be so I'll need to find the WHOLE first int value until the next special character/space in the string.

Any idea how I can go about doing this?

possible duplicate of SQL Extract substring

APH
– APH

2015-09-22 20:07:49 +00:00
Commented Sep 22, 2015 at 20:07 — APH
– APH, Commented Sep 22, 2015 at 20:07

Gordon Linoff · Accepted Answer · 2015-09-24 21:26:49Z

3

Assuming there really is a number in the string, you can use patindex():

select left(s, patindex('%[^0-9]%', s) - 1)
from (select substring(col, patindex('%[0-9]%', col), len(col)) as s
      from t
     ) t;

edited Sep 24, 2015 at 21:26

answered Sep 22, 2015 at 20:23

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JCBWS Over a year ago

That's it! However, I won't know the length argument in the substring syntax and would need the code to calculate that as well. Basically, I want it to grab the first WHOLE int (and possible decimal) value until the next special character/value (spaces, colons, semi-colons, hyphens, etc.) but ignore periods too so I can use decimal values.

JCBWS Over a year ago

Figured it out! Just simply had to use the LEN function on top of the PATINDEX function for the Length argument select SUBSTRING(col, patindex('%[0-9]%', col), len(patindex('%[0-9]%', col))) as s from t. Thanks for all your help, Gordon! :)

Collectives™ on Stack Overflow

Extract first number value from string sql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related