Remove set of characters between [abcd] in a variable

• sammm

  SSC-Addicted

  Points: 425

  More actions

  June 28, 2010 at 7:14 pm

  #97058

  I have a SP which gets parameter @business like this

  [abcdef] Client

  [gdhdnuidd] 1st Party SW

  testbusiness

  I want to get rid of the charcters from [ and ]. Is there a SQL function which searches for the open bracket[ and closing bracket ] and remove the whole set of characters.

  Is it possible. Appreciate your help on this,.

  The end result i want should be like this

  Client

  1st Party SW

  testbusiness

  Basically i want to remove the characters inside the brackets and the bracket too..

  --Sam

• WayneS

  SSC Guru

  Points: 95409

  More actions

  June 28, 2010 at 10:13 pm

  #1186142

  How does this work?

  declare @test-2 table (Col1 varchar(50))

  insert into @test-2

  select '[abcdef] Client' UNION ALL

  select '[gdhdnuidd] 1st Party SW' UNION ALL

  select 'testbusiness'

  select case when charindex(']', Col1) > 0 then substring(Col1, Charindex(']', Col1)+2, 50)

  else Col1 end

  from @test-2

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• ColdCoffee

  SSC-Dedicated

  Points: 39971

  More actions

  June 28, 2010 at 10:59 pm

  #1186150

  My 2 cents 🙂

  declare @table table(

  string varchar(50)

  )

  insert @table (string)

  select '[gdhdnuidd] 1st Party SW'

  union all select '[abcdef] Client'

  union all select 'testbusiness'

  select

  case

  when charindex(']',string) > 0 then right (string, (len(string)- charindex(']',string)))

  else string

  end finalstring

  from @table