September 10, 2008 at 8:40 am
Hey all,
I have a bunch of fields with lastname,first name in it.
I am trying to take first name place it in a new column named firstname then do the same with lastname.
The problem I have is I need the script to find what value '"," is in so that I can select substring(name,1,",") if that makes any sense lol....
September 10, 2008 at 8:47 am
CHARINDEX is what you are after:-
select substring(name,1,charindex(',',name)-1)
September 10, 2008 at 8:56 am
The charindex function should do what you want and it can be placed inside of the substring function so its results get passed directly to the substring function.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 10, 2008 at 8:59 am
How funny....
That is what i was messing with while waiting for replies....
select charindex(',',name,1)
from WritersNames
The odd part, some of the values return as ZERO....:ermm:
September 10, 2008 at 9:01 am
Ian Scarlett (9/10/2008)
CHARINDEX is what you are after:-select substring(name,1,charindex(',',name)-1)
select substring(name,1,charindex(',',name,1))
I think this is what you meant?
September 10, 2008 at 9:13 am
--Not very elegant but I hope this helps cut, copy the whole lot into
-- query analyzer to test
--I made a test1 table to hold a fullname
create table test1
(fullname varchar(80) null)
--inserted a test row
insert into test1(fullname)
values ('Sharon, Smith')
-- this selects the fullname to check it, then forename then surname
-- I used charindex to find where in the string ',' ocurred (7 in example)
-- then I used the result - 1 for the end point of a substring (forename)
-- result +2 ( for the space aswell) for start point of surname
select fullname, substring(fullname,1,charindex(',', fullname)-1), substring(fullname, charindex(',', fullname) +2, 100)
from test1
September 10, 2008 at 9:16 am
CharIndex returns a zero if the search string is not there. I'd check your data to see if they all have a comma in.
September 10, 2008 at 9:26 am
This also seemed to work also...
SELECT LEFT( name , CHARINDEX( ' ', name,-1 ))
from WritersNames
SELECT RIGHT( name , CHARINDEX( ' ',reverse(name)))
from WritersNames
September 10, 2008 at 9:35 am
Also, it doesn't seem to like the -1 length outside of the charindex statement..
September 10, 2008 at 3:17 pm
select substring(name,1,charindex(',',name,1))
I think this is what you meant?
No, I meant what I posted.
charindex(',',name) will give you the position of the comma, and the -1 backs it up by one to exclude the comma.
Try this:-
declare @name varchar(50)
set @name = 'Smith, Fred'
--this will return 'Smith'
select substring(@name,1,charindex(',',@name)-1)
--this will return 'Smith, '
select substring(@name,1,charindex(',',@name)+1)
September 11, 2008 at 7:19 pm
yea, the string functions in T-SQL are not vast, but if you work them, they can do the trick.
The more you are prepared, the less you need it.
September 11, 2008 at 9:20 pm
Believe this will handle both "Jones, Jack" and "Jones Jack" equally as well.
DECLARE @WritersNames AS VARCHAR(50)
DECLARE @Pos AS INT
SET @WritersNames = 'Jones Jack'
SET @Pos = (Select charindex(',',@Writersnames,1))
IF (@Pos = 0) -- Blank between names
BEGIN
select substring(@Writersnames,1,charindex(' ',@Writersnames,1))
END
ELSE -- Comma between names
BEGIN
select substring(@Writersnames,1,charindex(',',@Writersnames,1)-1)
END
Not shown, but other items to consider.
Combination of "Jones, Jack" - comma followed by 2 blanks
"Jones,Jack" - comma without following blank
"Jones Jack" -- no comma multiple blanks can use LTRIM to trim leading blanks ..
You should have enough to handle all of the above, so will leave it to you.
September 14, 2008 at 3:39 am
declare @name varchar(50)
set @name = 'Smith,Fred,John,J.Smith,Ann,Mary,Ken,Ted,quen,Kin'
What about separating more names?
Will the same query work?
September 14, 2008 at 9:07 am
To expand beyond the simple parsing of Last name, first name to what you have asked may I recommend reading an excellent article by Steve Jones at http://www.sqlservercentral.com/articles/Advanced+Querying/20010422115807/91/
Also consider Steve's statement in the above article
After all, shouldn't the validation be occurring in the presentation or business layers?
as the true solution.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply