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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 keep only first 3 characters

Author  Topic 

choideyoung
Starting Member

10 Posts

Posted - 2015-03-27 : 09:08:50
I need to write a script to modify the first and last names in my table (first and Last are separate fields). I currently store the full name and the decision has been made to only store the first 3 char of the first and last name.
I need a script that will accomplish this.

Currently I have
Frist Name Last Name
Drew Schiller

I need to update so that it keeps
Frist Name Last Name
Dre Sch

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-27 : 09:31:27
update table
set firstname = left(firstname, 3),
lastname = left(lastname, 3)

BTW what about collisions? e.g. Martin Jones and Mary Jonson both become Mar Jon
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-03-27 : 16:12:56
Apologies for my being pedantic on such things! but I would add

WHERE LEN(firstname) > 3 OR LEN(lastname) > 3

this won't then update any already-short names (probably unlikely!) but can also be run on the table in future, if it gets more "Long" names, without updating all the existing 3-character names (which just clogs up the Log file)

If the table is small just ignore me!
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-03-27 : 17:44:01
NEVER apologize for being pedantic...



I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-03-28 : 11:07:31
Sorry ... oh ... shouldn't have done that. Sorry.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-04-01 : 09:02:00
Why do not you SELECT them without changing the original data?

SELECT
firstname = left(firstname, 3),
lastname = left(lastname, 3)
from
TABLE

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -