Click here to Skip to main content
12,953,937 members (42,927 online)
Rate this:
 
Please Sign up or sign in to vote.
Another one for you guys, i have this on oracle:
SELECT SUBSTR('JOHN SCOTT JUNIOR',1,instr('JOHN SCOTT JUNIOR',' ',1,1) )
FROM dual;
SELECT SUBSTR('JOHN SCOTT JUNIOR',instr('JOHN SCOTT JUNIOR',' ',-1,2)+1,instr('JOHN SCOTT JUNIOR',' ',-1,1)-instr('JOHN SCOTT JUNIOR',' ',-1,2) )
FROM dual;
SELECT SUBSTR('JOHN SCOTT JUNIOR',INSTR('JOHN SCOTT JUNIOR',' ',-1,1)+1 )
FROM dual;


RESULTS
SUBSTR('JOHNSCOTTJUNIOR',1,INSTR('JOHNSCOTTJUNIOR','',1,1)) 
----------------------------------------------------------- 
JOHN 
                                                       
SUBSTR('JOHNSCOTTJUNIOR',INSTR('JOHNSCOTTJUNIOR','',-1,2)+1,INSTR('JOHNSCOTTJUNIOR','',-1,1)-INSTR('JOHNSCOTTJUNIOR','',-1,2)) 
------------------------------------------------------------------------------------------------------------------------------ 
SCOTT
                                                                                                                          
SUBSTR('JOHNSCOTTJUNIOR',INSTR('JOHNSCOTTJUNIOR','',-1,1)+1) 
------------------------------------------------------------ 
JUNIOR

I need to do this exact thing on SQL SERVER can anyone point me in the right direction?
Posted 3-May-11 7:07am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

PATINDEX[^] should be the T-SQL string function you're looking for!

Best Regards,

-MRB
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Well guys heres what i have so far, for my needs it works:
SELECT 
   left(substring('JOHN SCOTT JUNIOR',1,CHARINDEX(' ','JOHN SCOTT JUNIOR',1)),15) [first name],
   substring('JOHN SCOTT JUNIOR',CHARINDEX(' ','JOHN SCOTT JUNIOR',1)+1,15) [first name]

RESULTS
FIRST_NAME    LAST_NAME
JOHN          SCOTT JUNIOR

Still working on the middle one, gonna leave it here if someone wants to take it and continue... it works but for some weird cases it doesn't display middle names...
substring('JOHN SCOTT JUNIOR',CHARINDEX(' ','JOHN SCOTT JUNIOR',CHARINDEX(' ','JOHN SCOTT JUNIOR')),LEN('JOHN SCOTT JUNIOR')+15-CHARINDEX(' ','JOHN SCOTT JUNIOR',CHARINDEX(' ','JOHN SCOTT JUNIOR'))-CHARINDEX(' ',REVERSE('JOHN SCOTT JUNIOR'),1)+1) [Last name],
REVERSE(substring(REVERSE( LEFT('JOHN SCOTT JUNIOR',LEN('JOHN SCOTT JUNIOR')+1-CHARINDEX(' ',REVERSE('JOHN SCOTT JUNIOR'),1))),1,CHARINDEX(' ',REVERSE( LEFT('JOHN SCOTT JUNIOR',LEN('JOHN SCOTT JUNIOR')+1-CHARINDEX(' ',REVERSE('JOHN SCOTT JUNIOR'),1))),1)-1))    [last name],
REVERSE(substring(REVERSE('JOHN SCOTT JUNIOR'),1,CHARINDEX(' ',REVERSE('JOHN SCOTT JUNIOR'),1)-1))  [second last name],
  Permalink  
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,464
CHill60 3,490
Maciej Los 3,123
ppolymorphe 2,020
Jochen Arndt 1,975


Advertise | Privacy | Mobile
Web01 | 2.8.170525.1 | Last Updated 4 May 2011
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100