View previous topic :: View next topic
|
Author |
Message |
narasridhar
New User
Joined: 12 Oct 2006 Posts: 32 Location: India
|
|
|
|
How can we split a string by using SQL query? Please give a sample query with the example |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
use SUBSTR function .....
Select SUBSTR(Name,1,5) from table; here it will split the Col Name value strting from position 1 and 5 chars ...... |
|
Back to top |
|
|
narasridhar
New User
Joined: 12 Oct 2006 Posts: 32 Location: India
|
|
|
|
ashimer wrote: |
use SUBSTR function .....
Select SUBSTR(Name,1,5) from table; here it will split the Col Name value strting from position 1 and 5 chars ...... |
if we dint know the number of charecters to split ... the split will be based on the space middle of the name that case what we need to do? |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
can u b more specific ....
for using substr u need to knw the strting position n length .... |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
suggest you retrieve the complete column from DB2 and use a COBOL UNSTRING command. DB2 does not have a 'parse' command, and that is what you are looking for.
if you have a constant business need for spliting names, suggest you redefine your database. |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
NARA,
You mean something like this?
Code: |
DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL
(
NAME CHAR(20)
)
;
INSERT INTO SESSION.TEMP_TBL (NAME) VALUES ('GEORGE BUSH')
;
INSERT INTO SESSION.TEMP_TBL (NAME) VALUES ('NARA SRIDHAR')
;
SELECT SUBSTR(NAME,1,LOCATE(' ',NAME) - 1) AS FIRST_NAME,
SUBSTR(NAME,LOCATE(' ',NAME) + 1) AS LAST_NAME
FROM SESSION.TEMP_TBL
;
|
Results:
Code: |
+---------------------------------------------+
| FIRST_NAME | LAST_NAME |
+---------------------------------------------+
1_| GEORGE | BUSH |
2_| NARA | SRIDHAR |
+---------------------------------------------+
|
Dave |
|
Back to top |
|
|
narasridhar
New User
Joined: 12 Oct 2006 Posts: 32 Location: India
|
|
|
|
Thanks DavidatK |
|
Back to top |
|
|
MCEVOY
New User
Joined: 21 Nov 2005 Posts: 18
|
|
|
|
Try
SELECT SUBSTR(column,1,POSSTR(column,' ')-1),
SUBSTR(column,POSSTR(column,' ')+1)
The POSSTR function finds the position of one string within another. |
|
Back to top |
|
|
saraswathis
New User
Joined: 06 Sep 2006 Posts: 8
|
|
|
|
hi
Me too have the problem in splitting a string with spaces. But while doing in SPUFI (MF) in SUBSTR function as
SUBSTR(P.P_PAYEE_NAME,1,POSSTR(P.P_PAYEE_NAME,' ')-1)
or
SUBSTR(P.P_PAYEE_NAME,1,(POSSTR(P.P_PAYEE_NAME,' ')-1))
i m getting -138 error - THE SECOND OR THIRD ARGUMENT OF THE SUBSTR FUNCTION IS OUT OF RANGE.
pl. help me to solve this.
Thnks in adv. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Your code does not match David's solution. . .
Try changing this
Quote: |
SUBSTR(P.P_PAYEE_NAME,1,POSSTR(P.P_PAYEE_NAME,' ')-1)
or
SUBSTR(P.P_PAYEE_NAME,1,(POSSTR(P.P_PAYEE_NAME,' ')-1)) |
to follow David's example
Code: |
SELECT SUBSTR(NAME,1,LOCATE(' ',NAME) - 1) AS FIRST_NAME,
SUBSTR(NAME,LOCATE(' ',NAME) + 1) AS LAST_NAME
FROM SESSION.TEMP_TBL |
Notice that the example works but does not use POSSTR. |
|
Back to top |
|
|
|