SQL how to extract middle of string

Question

I want to only get the unit number out of this string:

'<p>The status for the Unit # 3546 has changed from % to OUTTOVENDOR</p>'

The note is always exactly the same but the unit number changes. How do I extract just the unit number?

Thanks!

Answer 1

declare @String varchar(500)

set @String = '<p>The status for the Unit # 3546 has changed from % to OUTTOVENDOR</p>'

select SUBSTRING(@String, charindex('# ', @String) + 2, charindex('has changed', @String) - charindex('# ', @String) - 2)

Answer 2

try:

  declare @S VarChar(1000)
  Set @S = 
  '<p>The status for the Unit # 3546 has changed from % to OUTTOVENDOR</p>'
  Select Substring( @s, 
         charIndex('#', @S)+1, 
         charIndex('has', @S) - 2 - charIndex('#', @S))