Extract string using SQL Server 2012-CodePudding

I have a string in the form of

<div>#FIRST#12345#</div>

How do I extract the number part from this string using T-SQL in SQL Server 2012? Note the number has variable length

CodePudding user response：

Shooting from the hip die to a missing minimal reproducible example.

Assuming that it is XML data type column.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
('<div>#FIRST#12345#</div>'),
('<div>#FIRST#770770#</div>');
-- DDL and sample data population, end

SELECT t.*
    , LEFT(x, CHARINDEX('#', x) - 1) AS Result
FROM @tbl t
    CROSS APPLY xmldata.nodes('/div/text()') AS t1(c)
    CROSS APPLY (SELECT REPLACE(c.value('.', 'VARCHAR(100)'), '#FIRST#' ,'')) AS t2(x);

Output

 ---- --------------------------- -------- 
| ID |          xmldata          | Result |
 ---- --------------------------- -------- 
|  1 | <div>#FIRST#12345#</div>  |  12345 |
|  2 | <div>#FIRST#770770#</div> | 770770 |
 ---- --------------------------- --------

CodePudding user response：

Using just t-sql string functions you can try:

create table t(col varchar(50))
insert into t select '<div>#FIRST#12345#</div>'
insert into t select '<div>#THIRD#543#</div>'
insert into t select '<div>#SECOND#3690123#</div>'

select col, 
  case when p1.v=0 or p2.v <= p1.v then '' 
    else Substring(col, p1.v, p2.v-p1.v) 
  end ExtractedNumber
from t
cross apply(values(CharIndex('#',col,7)   1))p1(v)
cross apply(values(CharIndex('#',col, p1.v   1)))p2(v)

Output:

Caveat, this doesn't handle any "edge" cases and assumes data is as described.