Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9200
  • Last Modified:

Substring and Index of in MSSQL 2005

Hi,

I am using MSSQL 2005

I have complete Names in table column of the database

I wanted to seperate firstname and lastname. They all are , seperated

I mean if I have Jain, Akalank

here 'Jain' is the last name and 'Akalank' is the first name

And more names are

Jain, Akalank
Soubigou, Alban
Plattner, Albert
Klatt, Alexander
Simon, Alex
David, Alexandre
Holness, Andrew
Evra, Antoine
Stracke, Armin
Pathammavong, Arom
Savaria, Benoit
Mueller, Boris
Abitbol-Zouari, Caroline
Eck, Christian
Pierce, Daniel
De Giorgio, Domenicantonio
Floridia, Emanuele
Davidson, Emma Louise
De Palma, Federico
Milio, Francesco
Ortone, Francesco
Melka, Frederic
Wilson, Glenn
Rodriguez San Pedro, Jaime
NULL

How can we substring these names in 2 columns

Please help me with query

Many Thanks
0
tia_kamakshi
Asked:
tia_kamakshi
2 Solutions
 
udaya kumar laligondlaTechnical LeadCommented:
use
select Left(FieldName,CharIndex(',', FieldName)-1) , Right(FieldName,Len(FieldName) -CharIndex(',', FieldName))  from tableName
0
 
adatheladCommented:
Hi,

Given this a quick test, and it works if the column contains nulls or records without a comma in them...
SELECT *,
	CASE 
		WHEN CHARINDEX(',', YourField) > 0 THEN
			LEFT(YourField, CHARINDEX(',', YourField) - 1)
		ELSE YourField
	END AS FirstPart,
	CASE 
		WHEN CHARINDEX(',', YourField) > 0 THEN
			LTRIM(SUBSTRING(YourField, CHARINDEX(',', YourField) + 1, LEN(YourField)))
		ELSE
		''
	END AS SecondPart
FROM YourTable

Open in new window

0
 
tia_kamakshiAuthor Commented:
Many Thanks. Its great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now