sql server: take address extension into different column
sql server: take address extension into different column
I have some address numbers that I need to split, currently the format is like:
col a: 42A
col b: NULL
I want to put the addressextension in column B, and remove it from A. What's the easiest way to do this in MS Sql?
So an update statement that removes the value (in the example: 'A') from column A, and puts them in clumn B, and keeps the number in A. –
Can we assume you mean an update to the rows? Where column B IS NULL, but column A IS NOT NULL.
– Used_By_Already
Sep 10 '18 at 6:35
I mean an update indeed, apologies! So an update statement that removes the value (in the example: 'A') from column A, and puts them in clumn B, and keeps the number in A.
– stunnie
Sep 10 '18 at 6:36
Please share your raw data and expected output with conditions
– gulshan arora
Sep 10 '18 at 6:39
update yourtable set cola=substring(cola, 1,len(cola)-1),colb=substring(cola,len(cola),len(cola)) where colb is null
– Raghavendra
Sep 10 '18 at 6:51
4 Answers
4
Might be below query will help you.
create table yourtable (
cola varchar(10), colb varchar(10)
)
;
insert into yourtable
values ('42a', NULL)
insert into yourtable
values ('42aa', NULL)
insert into yourtable
values ('42aaa', NULL)
insert into yourtable
values ('442aaa', NULL)
UPDATE yourtable
SET cola = LEFT(cola, PATINDEX('%[0-9][^0-9]%', cola ))
,colb= LTRIM(RIGHT(cola, LEN(cola) - PATINDEX('%[0-9][^0-9]%', cola )))
select *
from yourtable
Gotcha, thanks!!
– stunnie
Sep 10 '18 at 7:14
Just use an update, but take care to set colb first, then you can set cola to null.
e.g.
create table yourtable (
cola varchar(10), colb varchar(10)
)
;
insert into yourtable
values ('42a', NULL)
;
select *
from yourtable
;
| cola | colb |
+------+------+
| 42a | NULL |
update yourtable
set colb = cola
, cola = NULL
where cola IS NOT NULL
and colb IS NULL
;
select
*
from yourtable
;
| cola | colb |
+------+------+
| NULL | 42a |
Hey! Thanks for you response, i think i may have been unclear, I mean to just move the letter to another column. For example, in COLA =42A, and COLB should be: 'A' after the statement, and COLA should just be 42. (length of numbers is variable)
– stunnie
Sep 10 '18 at 6:50
If we assume that the structure of ColA is permanent and it has 2digits plus one character always, you can simply run this query:
UPDATE [yourTable] SET COLA = SUBSTRING(COLA,1,2) , COLB = substring(COLA,3,1)
You could try the follwing:
CREATE TABLE [yourtable]
([cola] VARCHAR(10),
[colb] VARCHAR(10)
);
INSERT INTO [yourtable]
VALUES
('42a',
NULL
);
SELECT *
FROM [yourtable];
UPDATE [yourtable]
SET
[colb] = RIGHT([cola], 1),
[cola] = LEFT([cola], 2)
WHERE [cola] = '42a';
Answer from @Nikunj is probably the most versatile solution.
– Dan Stef
Sep 10 '18 at 6:53
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
What is the expected result?
– Jens
Sep 10 '18 at 6:34