Search code examples
sql-serverstringcharnvarchar

Extract string between chars in SQL Server strings

Can some one kindly suggest ways by which I can extract 0459 and 0460 from a string like (&0459&/&0460&)*100 in a SQL Server table?

I should be able to pull out two strings sandwiched between two pair of ampersands.

Thanks in advance.

Solution

  • If you are certain there are always two pair of ampersands you may extract the two strings like this

    declare @s varchar(max) = '(&0459&/&0460&)*100'

declare @first int = charindex('&', @s)
declare @second int = charindex('&', @s, @first+1)
declare @third int = charindex('&', @s, @second+1)
declare @fourth int = charindex('&', @s, @third+1)

select substring(@s, @first+1, @second-@first-1)
select substring(@s, @third+1, @fourth-@third-1)