Solved

Substring and Index of in MSSQL 2005

Posted on 2009-04-03
3
9,158 Views
Last Modified: 2012-05-06
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
Comment
Question by:tia_kamakshi
3 Comments
 
LVL 12

Accepted Solution

by:
udaya kumar laligondla earned 250 total points
ID: 24058720
use
select Left(FieldName,CharIndex(',', FieldName)-1) , Right(FieldName,Len(FieldName) -CharIndex(',', FieldName))  from tableName
0
 
LVL 23

Assisted Solution

by:adathelad
adathelad earned 250 total points
ID: 24058754
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
 

Author Closing Comment

by:tia_kamakshi
ID: 31566197
Many Thanks. Its great
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question