i want to find a number from a string and get the number beside that letter in sql server

Question

hi I want to find the number beside a letter in a string , like I have

"LOTS 1, 2 & 3 -50 X 140 EACH-"                 
"PLOTS 17 & 18 -50 X150' EACH-"

these kind of data in a column . now I want to find X from the data and pull the number which is right to the letter X. please let me know how to write a query in sql server to solve this .

thanks in advance.

Answer 1

This certainly is not pretty, but it works without subqueries. Using charindex() and patindex() with substring():

create table t (str varchar(64));
insert into t values 
 ('LOTS 1, 2 & 3 -50 X 140 EACH-')
,('PLOTS 17 & 18 -50 X150'' EACH-');

select 
    number = case when charindex('X',str)>0 then 
              left(ltrim(substring(str,charindex('X',str)+1,8000))
             ,patindex('%[^0-9]%',ltrim(substring(str,charindex('X',str)+1,8000)))-1
             )
             else null 
             end
from t

rextester demo: http://rextester.com/CHFO33611

returns:

+--------+
| number |
+--------+
|    140 |
|    150 |
+--------+