Extract string using SQL Server 2012

Question

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

Answer 1

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.