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.
2
-
I recommend that you read about basic SQL string manipulation functions and basic update statement. There are also few examples on SO.Eduard Uta– Eduard Uta2017-03-10 13:14:23 +00:00Commented Mar 10, 2017 at 13:14
-
You can find many examples about it. Please google it and you wil find many things.Dogan– Dogan2017-03-10 13:34:08 +00:00Commented Mar 10, 2017 at 13:34
Add a comment
|
3 Answers
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 |
+-----+-----+
Comments
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))
Comments
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!
