CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012substring
Tony Clifton
Tony Clifton

Reputation: 703

TSQL - Extract ID that starts with specific char and has a certain length from string

I want to extract badly entered IDs from a column. I know that the ID always starts with E0, and is always followed by exactly four digits.

Here's my approach so far:

  declare @t table(myvalue varchar(100))
  insert @t values('_badE04746_bad')
  select * from @t
  select stuff(myvalue, 1,patindex('%[^0-9]E0[0-9]%', myvalue + '0'), '')
  from @t

I'm able to remove the part before the E0 but not behind. I failed in trying to integrate the substring function to specify a certain length.

Hopefully you can put me on the right track here.

Thanks for your help in advance.

Upvotes: 1

Views: 83

Answers (1)

Ocaso Protal
Ocaso Protal

Reputation: 20256

Since your ID is always 6 characters long ('E0' + 4 digits) you can use an additional substring:

  declare @t table(myvalue varchar(100))
  insert @t values('_badE04746_bad')
  select * from @t
  select SUBSTRING(stuff(myvalue, 1,patindex('%[^0-9]E0[0-9]%', myvalue + '0'), ''),1,6)
  from @t

Upvotes: 1

Related Questions