SQL Server Performance Forum – Threads Archive
split name
HiI have a name field which has data like ‘Richard, Gayle’. I want to splt this name into fields like lastName and firstName and insert into 2 different fields. How can query that? Thanks!
"He laughs best who laughs last"
You can very well handle this in your Front End application if any Otherwise Declare @str varchar(100)
set @str=’Richard, Gayle’
Select @str as Full_Name,
Substring(@str,1,charindex(‘, ‘,@str,1)-1) as First_name,
Substring(@str,charindex(‘, ‘,@str,1)+2,len(@str)) as Last_name
Madhivanan Failing to plan is Planning to fail
Actually the thing is I have to delete the NAME field and add 2 new columns with LASTNAME and FIRST NAME. So I need to get data into new fields from the old NAME field, and thought of doing this from database, i dont need that from front end app. Thanks!
"He laughs best who laughs last"
I need a query so that i can update all my rows in a table not just a single row in this manner. Thanks!
"He laughs best who laughs last"
Then why do you want to delete that column?
Use the logic I used so that you dont need to seperate columns Madhivanan Failing to plan is Planning to fail
The thing is i want to change my design in that way like deleting the old column NAME and adding 2 new columns LAST and FIRST. once i move the data to new columns then want to delete tha old column Thanks!
"He laughs best who laughs last"
Create two columns. Then run this Update yourTable
Set FirstName=Substring(NameCol,1,charindex(‘, ‘,NameCol,1)-1),
SecondName=Substring(NameCol,charindex(‘, ‘,NameCol,1)+2,len(NameCol)) Then run select Select * from yourTable If you are satisfied with the result drop Namecol Madhivanan Failing to plan is Planning to fail
I am getting the following error when i run that query, what does it mean. Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.
Thanks!
"He laughs best who laughs last"
Some of your Names dont have comma. Post such data here Madhivanan Failing to plan is Planning to fail
Hm, I can’t see anything wrong with Madhivanan’s statement. Does your column allow for NULL? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
yeah u r right, i observed such data in it. Bailey, Amy (Ann)
Mattucci, Emile J.
Cressinger. Lisa
Thanks!
"He laughs best who laughs last"
YEah Frank my column do allow nulls Thanks!
"He laughs best who laughs last"
So Cressinger. Lisa need to splitted as Cressinger and Lisa Then Update yourTable
Set FirstName=Substring(replace(NameCol,’.’,’,’),1,charindex(‘, ‘,replace(NameCol,’.’,’,’),,1)-1),
SecondName=Substring(replace(NameCol,’.’,’,’),,charindex(‘, ‘,replace(NameCol,’.’,’,’),,1)+2,len(NameCol))
Madhivanan Failing to plan is Planning to fail
still i get an error like Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ‘,’.
Thanks!
"He laughs best who laughs last"
If you can’t improve the quality of your raw data, I would probably consider doing this in a scripting language where you can use regular expressions. Your T-SQL code might become really ugly. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
My data is unique now except few rows where it has "." instead of ",".
I am just getting the syantax error now Thanks!
"He laughs best who laughs last"
Try this Update yourTable
Set FirstName=Substring(replace(NameCol,’.’,’,’),1,charindex(‘, ‘,replace(NameCol,’.’,’,’),1)-1),
SecondName=Substring(replace(NameCol,’.’,’,’),charindex(‘, ‘,replace(NameCol,’.’,’,’),1)+2,len(NameCol))
Madhivanan Failing to plan is Planning to fail
try adding WHERE NameCol LIKE ‘%,%’
to the updates, then it will only update rows that are correct.
Then you can check remainging rows with SELECT *
FROM …
WHERE FirstName IS NULL /* OR perhapt = ” */
if its a few, just update those rows manually.
If its many, either apply some logic or just set ‘SecondName’ to the entire contents and leave ‘FristName’ blank… /L —
http://anticAPSLOCK.com
Dear All,I want any one support on the following case :i have a colomn that contains arabic names (the name starts from the right) and the seperator between the names are (Space) and i want to split this name into first, second, third, fourth and fifth name and meanehile some of these names have no spaces so how can i exclude them or even deal with them with any way .Any one feedback will be highly appreciated .thanks .
Please start a new thread with sample data and required output. This will improve your chances getting good replies in a timely manner.
]]>