Pulling specific terms from a string-SQL

Question

I am having a column of Full name like:

Fullname
-----------------------
Joel (MD),Garris
Yong (MD),Park
Kristen (DO),Kenny
Jr, Jack (MD),Freimann
FirstName (title), LastName

What I actually want is FirstName only. I have tried following but cant get the result.

I tried using CHARINDEX and SUBSTRING but still dint find any result:

Replace(SUBSTRING(@aa,1,(CHARINDEX('(',@aa))),',','')

Tried to pull title seperately and then tried to replace it by null:

Replace(FullName,SUBSTRING(Fullname, CHARINDEX('(',Fullname,0),CHARINDEX(')',Fullname,CHARINDEX('(',Fullname,0))),'')

Can anyone tell me what is going wrong in this?

Zohar Peled · Accepted Answer · 2017-01-19 07:58:33Z

4

I'm not sure why you need the replace, but here is a use of substring that will get you only the first name:

Create and populate sample table (Please save us this step in your future questions)

DECLARE @T As TABLE
(
    Fullname varchar(30)
)

INSERT INTO @T VALUES
('Joel (MD),Garris'),
('Yong (MD),Park'),
('Kristen (DO),Kenny'),
('Jr, Jack (MD),Freimann'),
('FirstName (title), LastName')

The query: (note the use of case to avoid errors if there is no ( in the string)

SELECT CASE WHEN CHARINDEX('(', FullName) > 1 THEN
           SUBSTRING(FullName, 1, CHARINDEX('(', FullName) - 1)
       ELSE
           FullName
       END As FirstName
FROM @T

Results:

FirstName
Joel 
Yong 
Kristen 
Jr, Jack 
FirstName

answered Jan 19, 2017 at 7:58

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nolan Shang · Accepted Answer · 2017-01-19 09:01:33Z

2

Another sample that parse with PARSNAME

declare @tb table (Fullname varchar(400))    
    Insert into @tb values ('Joel (MD),Garris')
    Insert into @tb values ('Yong (MD),Park')
    Insert into @tb values ('Kristen (DO),Kenny')
    Insert into @tb values ('Jr, Jack (MD),Freimann')
    Insert into @tb values ('FirstName (title), LastName')

select PARSENAME(REPLACE(Fullname,'(','.'),2) from @tb

Joel 
Yong 
Kristen 
Jr, Jack 
FirstName

answered Jan 19, 2017 at 9:01

Nolan Shang

2,3281 gold badge14 silver badges10 bronze badges

Comments

Ranjana Ghimire · Accepted Answer · 2017-01-19 08:03:01Z

0

Try splitting string from '('

   declare @name table (Fullname varchar(400))    
    Insert into @name values ('Joel (MD),Garris')
    Insert into @name values ('Yong (MD),Park')
    Insert into @name values ('Kristen (DO),Kenny')
    Insert into @name values ('Jr, Jack (MD),Freimann')
    Insert into @name values ('FirstName (title), LastName')

select substring (fullname,0,charindex('(',fullname)) from @name

answered Jan 19, 2017 at 8:03

Ranjana Ghimire

1,8451 gold badge14 silver badges22 bronze badges

Comments

Owner2229 · Accepted Answer · 2017-01-19 08:04:07Z

0

Try this:

SELECT SUBSTRING(Fullname, 1, CASE CHARINDEX('(', Fullname)
   WHEN 0
      THEN LEN(Fullname)
   ELSE CHARINDEX('/', Fullname) - 1
   END) AS FirstName 
FROM MyTable

answered Jan 19, 2017 at 8:04

Owner2229

11 bronze badge

Comments

Yatin Patil · Accepted Answer · 2017-01-19 08:18:28Z

Let's say you are having following table structure;

CREATE TABLE #temp (Fullname VARCHAR(50))

and

INSERT INTO #temp VALUES ('Joel (MD),Garris')
INSERT INTO #temp VALUES ('Yong (MD),Park')
INSERT INTO #temp VALUES ('Kristen (DO),Kenny')
INSERT INTO #temp VALUES ('Jr, Jack (MD),Freimann')
INSERT INTO #temp VALUES ('FirstName (title), LastName ')
INSERT INTO #temp VALUES ('aaa, bbb')

Get FirstName only from table as:

SELECT CASE 
        WHEN charindex('(', Fullname, 1) = 0
            THEN Fullname
        ELSE LEFT(Fullname, charindex('(', Fullname, 1) - 2)
        END AS FirstNameOnly
FROM #temp

I have used charindex to get index of '(' and then I have used LEFT function to get characters of Fullname starting from left to that character index, i have deducted additional one index to get rid of space after firstname.

I have used case when then, in case you don't have stored '(' in your column. You can remove that case when then, if you are sure that, you'll always have '(' stored in your column as:

SELECT LEFT(Fullname, charindex('(', Fullname, 1) - 2) AS FirstNameOnly
FROM #temp

Collectives™ on Stack Overflow

Pulling specific terms from a string-SQL

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related