CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Nicolas
Nicolas

Reputation: 2376

How can I CUT a specific string part to another column in SQL?

I have about 500 records in a table with an nvarchar column.

I want to cut a part of that data into another column. by "cut" I mean deleting it in the original column and add it to the target column.

All the data that has to be cut is contained within brackets. The bracketed text may occur anywhere in the string.

For example, ColumnA has: SomeTest Data [I want to cut this], and I want to move [I want to cut this] (but without the brackets) to ColumnB.

How do I achieve this?

UPDATE

Eventually found it out. The problem was that I didn't escaped my brackets.
What I have now (and works):

UPDATE TableA
SET TargetColumn = substring(SourceColumn,charindex('[',SourceColumn)+1,charindex(']',SourceColumn)-charindex('[',SourceColumn)-1),
SourceColumn = substring(SourceColumn, 0, charindex('[',SourceColumn))
where TableA.SourceColumn like '%\[%\]%' ESCAPE '\'

Upvotes: 1

Views: 2205

Answers (3)

JosephStyons
JosephStyons

Reputation: 58685

You left some questions unanswered.

  • How do you want to handle NULL values? I am leaving them NULL.
  • Where should the 'cut' string go? I am assuming "at the end"
  • What do you do if you find nested brackets? [[cut me]]
  • Do you need to remove any surrounding spaces? For example, does "The cat [blah] sleeps" become "The cat**sleeps" with two spaces before "sleeps"?

To make the operation atomic, you'll want to use a single UPDATE.
Here is a sample script to get you started.

--build a temp table with sample data
declare @t table(ikey int, sourcecolumn nvarchar(100), targetcolumn nvarchar(100));
insert into @t
      select 0,'SomeTest Data [I want to cut this]','Existing Data For Row 1'
union select 1,'SomeTest [cut this too] Data2','Existing Data For Row 2'
union select 2,'[also cut this please] SomeTest Data3',null
union select 3,null,null
union select 4,null,''
union select 5,'Nested bracket example [[[within nested brackets]]] Other data',null
union select 6,'Example with no brackets',null
union select 7,'No brackets, and empty string in target',''

--show "before"
select * from @t order by ikey

--cut and paste
update @t
set
  targetcolumn =
    isnull(targetcolumn,'') +
      case when 0 < isnull(charindex('[',sourcecolumn),0) and 0 < isnull(charindex(']',sourcecolumn),0)
           then substring(sourcecolumn,charindex('[',sourcecolumn)+1,charindex(']',sourcecolumn)-charindex('[',sourcecolumn)-1)
           else ''
           end
 ,sourcecolumn =
    case when sourcecolumn is null
         then null 
         else substring(sourcecolumn,0,charindex('[',sourcecolumn)) + substring(sourcecolumn,charindex(']',sourcecolumn)+1,len(sourcecolumn))
         end
where sourcecolumn like '%[%'
  and sourcecolumn like '%]%'

--show "after"
select * from @t order by ikey

Upvotes: 1

TheGuyThatDoesn&#39;tKnowMuch
TheGuyThatDoesn&#39;tKnowMuch

Reputation: 1250

An UPDATE statement along these lines would do it:

CREATE TABLE #Test
(
    StringToCut VARCHAR(100)
    ,CutValue VARCHAR(100)
)

INSERT #Test
VALUES
('SomeTest Data 1 [I want to cut this 1] More Testing',NULL),
('SomeTest Data 2 [I want to cut this 2]',NULL),
('SomeTest Data 3 [I want to cut this 3] Additional Test',NULL),
('[I want to cut this 4] last test',NULL)

SELECT * FROM #Test

--Populate CutValue column based on starting position of '[' and ending position of ']'
UPDATE #Test
SET CutValue = SUBSTRING(StringToCut,CHARINDEX('[',StringToCut),(CHARINDEX(']',StringToCut)-CHARINDEX('[',StringToCut)))

--Remove the '[' ']'
UPDATE #Test
SET CutValue = REPLACE(CutValue,'[','')
UPDATE #Test
SET CutValue = REPLACE(CutValue,']','')

--Remove everything after and including '[' from StringToCut
UPDATE #Test
SET StringToCut = LEFT(StringToCut,CHARINDEX('[',StringToCut)-1) + LTRIM(RIGHT(StringToCut,LEN(StringToCut)-CHARINDEX(']',StringToCut)))

SELECT * FROM #Test

DROP TABLE #Test

Upvotes: 3

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

And another one in single update statement -

CREATE TABLE #Test
(
    StringToCut VARCHAR(50)
    ,CutValue VARCHAR(50)
)

INSERT #Test
VALUES
('SomeTest Data 1 [I want to cut this 1]',NULL),
('SomeTest Data 2 [I want to cut this 2]',NULL),
('SomeTest Data 3 [I want to cut this 3]',NULL),
('SomeTest Data 4 [I want to cut this 4]',NULL)

UPDATE #Test
SET CutValue = 
     SUBSTRING(StringToCut, CHARINDEX('[', StringToCut)+1, CHARINDEX(']', StringToCut) - CHARINDEX('[', StringToCut) - 1)

SELECT * FROM #Test

Upvotes: 0

Related Questions