removing brakets from data in select

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  April 16, 2012 at 2:47 pm

  #260148

  Hi

  I have a table showing data inside brackets, I want to remove the brakets and just keep the data inside brackets

  here is some sample:

  CREATE TABLE [dbo].[test](

  [F1] [varchar](500) NULL,

  [F2] [varchar](500) NULL,

  [F3] [varchar](500) NULL

  )

  INSERT INTO test ( F1,F2,F3) VALUES ( 'FOLDERS(W)', 'FOLDERS(5 WILWOOD)', 'DOCUMENT(204)')

  INSERT INTO test ( F1,F2,F3) VALUES ( 'FOLDERS(W)', 'FOLDERS(460 WALKER AVE)', 'DOCUMENT(258)')

  INSERT INTO test ( F1,F2,F3) VALUES ( 'FOLDERS(B)', 'FOLDERS(54 BY DRIVE)', 'DOCUMENT(276)')

  INSERT INTO test ( F1,F2,F3) VALUES ( 'FOLDERS(B)', 'FOLDERS(55 BY DRIVE)', 'DOCUMENT(201)')

  INSERT INTO test ( F1,F2,F3) VALUES ( 'FOLDERS(W)', 'FOLDERS(20 WILS DR)', 'DOCUMENT(292)')

  INSERT INTO test ( F1,F2,F3) VALUES ( 'FOLDERS(L)', 'FOLDERS(1425 L RD)', 'DOCUMENT(294)')

  INSERT INTO test ( F1,F2,F3) VALUES ( 'FOLDERS(R)', 'FOLDERS(3 RI WAY)', 'DOCUMENT(271)')

  INSERT INTO test ( F1,F2,F3) VALUES ( 'FOLDERS(L)', 'FOLDERS(1405 LOR FERY RD)', 'DOCUMENT(260)')

  INSERT INTO test ( F1,F2,F3) VALUES ( 'FOLDERS(W)', 'FOLDERS(9 WINDY BUSH WAY)', 'DOCUMENT(205)')

  INSERT INTO test ( F1,F2,F3) VALUES ( 'FOLDERS(S)', 'FOLDERS(300 SCOH RD)', 'DOCUMENT(245)')

  In this case FOLDERS() and DOCUMENT() should be removed only inside part should remain ...

  Any help

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  April 16, 2012 at 2:51 pm

  #1474412

  Thanks for the ddl and sample data. Very refreshing to see somebody post a question making it so easy to answer.

  This works on your sample data.

  select replace(REPLACE(F1, 'FOLDERS(', ''), ')', '') as F1,

  replace(REPLACE(F2, 'FOLDERS(', ''), ')', '') as F2,

  replace(REPLACE(F3, 'DOCUMENT(', ''), ')', '') as F3

  from test

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  April 17, 2012 at 7:22 am

  #1474710

  Thanks a lot for the reply...works like a charm

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  April 17, 2012 at 7:33 am

  #1474713

  You're welcome. 😀

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/