Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Substring and Index of in MSSQL 2005

Posted on 2009-04-03
3
Medium Priority
?
9,188 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 12

Accepted Solution

by:
udaya kumar laligondla earned 1000 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 1000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

604 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