CODEWITHSUNDEEP
sql-server
Urh Lednik
Urh Lednik

Reputation: 47

mssql string split and update columns

I have 2 columns - first one column A contains strings "abc/def" and second column B is empty. I would like to split strings and update columns with following results:

first column A: abc second column B: def

For all column A entries.

Upvotes: 2

Views: 4060

Answers (3)

Carlos
Carlos

Reputation: 1822

Try this:

select SUBSTRING('abc/def', 0, CHARINDEX('/','abc/def'))
select SUBSTRING('abc/def', CHARINDEX('/','abc/def')+1, len('abc/def'))

The first one for column A and the second one for column B

UPDATED with your update:

update myTable set ColumnA = SUBSTRING(ColumnA, 0, CHARINDEX('/',ColumnA)), ColumnB= SUBSTRING(ColumnA, CHARINDEX('/',ColumnA)+1, len(ColumnA))

Upvotes: 1

M84
M84

Reputation: 745

You can try with "SUBSTRING"...

   update <table> set 
       columnB = (select SUBSTRING(columnA, CHARINDEX('/',columnA)+1, len(columnA)) from <table>),
       columnA = (select SUBSTRING(columnA, 0, CHARINDEX('/',columnA)) from <table>);

or look this link for use STRING_SPLIT transact-Sql..

Hope this help!

Upvotes: 0

SqlZim
SqlZim

Reputation: 38043

Using charindex() to find the location of the delimiter, left() to get the left part, and stuff() to get the remaining part:

update t
set a = left(a, charindex('/',a+'/')-1)
  , b = stuff(a, 1,charindex('/',a+'/'),'')
;

select * from t;

rextester demo: http://rextester.com/WZFPH72760

returns:

+-----+-----+
|  a  |  b  |
+-----+-----+
| abc | def |
+-----+-----+

Upvotes: 2

Related Questions