Click here to Skip to main content
12,360,321 members (50,114 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL SQL-Server Oracle
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


Advertise | Privacy | Mobile
Web02 | 2.8.160621.1 | Last Updated 4 May 2011
Copyright © CodeProject, 1999-2016
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