column with part of the value from other

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

column with part of the value from other

Author

Topic

rajendrarama
Starting Member

14 Posts

Posted - 2012-05-31 : 12:17:44

I have a table with

ORDERID _NAME CUSTOMER_SURNAME

1 MR/MRS BADACH
2 MR ROCHFORD & MISS HENDERSON
3 MR/MRS PATERSON
4 MISS JONES & MISS QUINLIVAN
5 MR/MRS BRADLEY
6 MR/MRS CLEECE
7 MR/MRS FOWLER
8 D JONES
9 K COUCHLIN
10 CLARKE
11 L WRIGHT

can we get the last word to be transferred to customer_surname column(it's blank for now)
with the _name being the same

_NAME CUSTOMER_SURNAME
MR/MRS BADACH BADACH
MR ROCHFORD & MISS HENDERSON HENDERSON
MR/MRS PATERSON PATERSON
MISS JONES & MISS QUINLIVAN QUINLIVAN
MR/MRS BRADLEY BRADLEY
MR/MRS CLEECE CLEECE
MR/MRS FOWLER FOWLER
D JONES JONES
K COUCHLIN COUCHLIN
CLARKE CLARKE
L WRIGHT WRIGHT

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-31 : 13:18:00

update tbl
set CUSTOMER_SURNAME = right(_NAME, len(_NAME)-charindex(' ',reverse(_NAME))-1)
where _NAME like '% %'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2012-05-31 : 14:48:03

Nigel, yours didn't quite work as far as I could tell. There probably is a more elegant solution, but this should work for the sample provided:


declare @t table (ORDERID int, _NAME varchar(30), CUSTOMER_SURNAME varchar(20))
insert @t (ORDERID, _NAME)

select 1, 'MR/MRS BADACH' union
select 2, 'MR ROCHFORD & MISS HENDERSON' union
select 3, 'MR/MRS PATERSON' union
select 4, 'MISS JONES & MISS QUINLIVAN' union
select 5, 'MR/MRS BRADLEY' union
select 6, 'MR/MRS CLEECE' union
select 7, 'MR/MRS FOWLER' union
select 8, 'D JONES' union
select 9, 'K COUCHLIN' union
select 10, 'CLARKE' union
select 11, 'L WRIGHT'

update @t
set customer_surname = 
case
	when charindex(' ', _name) > 0 then reverse(substring(reverse(_name), 0, charindex(' ', reverse(_name)))) 
	else _name
end
from @t

select * from @t

Some days you're the dog, and some days you're the fire hydrant.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 15:29:34

[code]
update @t
set customer_surname = REVERSE(STUFF(REVERSE(_NAME),CHARINDEX(' ',REVERSE(_NAME)),LEN(_NAME),''))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2012-05-31 : 15:54:59

The above still doesn't account for case #10 where you get a NULL

Some days you're the dog, and some days you're the fire hydrant.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 16:06:13

quote:
Originally posted by Skorch

The above still doesn't account for case #10 where you get a NULL

Some days you're the dog, and some days you're the fire hydrant.


update @t
set customer_surname = REVERSE(STUFF(REVERSE(_NAME),CHARINDEX(' ',REVERSE(_NAME) + ' '),LEN(_NAME),''))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajendrarama
Starting Member

14 Posts

Posted - 2012-05-31 : 17:09:46

I am still getting the value as null for case #10(CLARKE)

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2012-05-31 : 17:45:15

Have you tried using what I posted?


update @t
set customer_surname = 
case
	when charindex(' ', _name) > 0 then reverse(substring(reverse(_name), 0, charindex(' ', reverse(_name)))) 
	else _name
end
from @t

Some days you're the dog, and some days you're the fire hydrant.

rajendrarama
Starting Member

14 Posts

Posted - 2012-06-01 : 04:11:12

Thank you. It has updated the table. For any new row insertion in the future,
how can i automatically update the surname column?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 11:25:05

quote:
Originally posted by rajendrarama

Thank you. It has updated the table. For any new row insertion in the future,
how can i automatically update the surname column?

for that why not create it as a computed column then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajendrarama
Starting Member

14 Posts

Posted - 2012-06-04 : 07:31:14

Could you please let me know how can i create a computed column such that a new row insertion with _name should automatically fill the surname column with the above logic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 10:00:14

when you create a computed column the column wont store value physically unless it is PERSISTED but it will be calculated on the fly while selecting from table.

So you dont need to (or cant) explicitly insert into computed column.

in your case it will be like


ALTER TABLE tablename ADD customer_surname AS case
	when charindex(' ', _name) > 0 then reverse(substring(reverse(_name), 0, charindex(' ', reverse(_name)))) 
	else _name
end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajendrarama
Starting Member

14 Posts

Posted - 2012-06-06 : 09:57:20

If the user has entered a space after the name, then it is not updating the customer_surname. could you please help with it.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-06 : 13:29:09

create trigger tr_tbl on tbl for insert, update
as
update tbl
set _name = reverse(substring(reverse(t._name), 0, charindex(' ', reverse(t._name))))
from tbl t
join inserted i
on t.pk = i.pk
where charindex(' ', t._name) > 0

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-07 : 22:34:31

quote:
Originally posted by rajendrarama

If the user has entered a space after the name, then it is not updating the customer_surname. could you please help with it.

you mean with computed column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources