Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to split a string in sql query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
saraswathis

New User


Joined: 06 Sep 2006
Posts: 8

PostPosted: Thu Apr 26, 2007 11:57 am    Post subject: How to split a string in sql query
Reply with quote

hi
I m having a field in DB2 table of length 22. It consists of a name in 3 parts last name, middle initial, first name. e.g., "JAMES A HARVILLE". I need to split the last name and first name separately in the sql query itself - i.e., in the SELECT statement. Please help me to sort this out.

Thanks in advance.
Back to top
View user's profile Send private message

ashwinreddy

Active User


Joined: 16 Sep 2004
Posts: 106
Location: Hyderabad

PostPosted: Thu Apr 26, 2007 12:19 pm    Post subject:
Reply with quote

Hi,

If you know the extact length of the First,Middle and last names. I think you can use Sub string function as

SUBSTR(column name,1,8)

we will check for any optimised solution from other members.

Cheers
Ashwin
Back to top
View user's profile Send private message
saraswathis

New User


Joined: 06 Sep 2006
Posts: 8

PostPosted: Thu Apr 26, 2007 12:23 pm    Post subject: split a string in sql query
Reply with quote

but this can be used only for this string. Each field may have different names where the first part would be of different length. how the query can be generalised. is there any UNSTRING function or truncate. delimiter function to serve the purpose?

thanks in adv.
Back to top
View user's profile Send private message
ashwinreddy

Active User


Joined: 16 Sep 2004
Posts: 106
Location: Hyderabad

PostPosted: Thu Apr 26, 2007 12:40 pm    Post subject:
Reply with quote

Hi,

My intension was to give you an idea that this can be possible through Substring

full solution as

SELECT substr(Field1,1,3) as First,
substring(Field1,4,3) as Middle,
substring(Field1,8,3) as last
From table name


Hope this will helps.

Cheers
Ashwin
Back to top
View user's profile Send private message
ashwinreddy

Active User


Joined: 16 Sep 2004
Posts: 106
Location: Hyderabad

PostPosted: Thu Apr 26, 2007 12:43 pm    Post subject:
Reply with quote

Hi,

I haven't tested the query as i don't have access to DB2 in my Project.

Please test it and let me know if it works, i think it can be implemented also as

SELECT substr(Field1,1,3) First,
substr(Field1,4,3) Middle,
substr(Field1,8,3) last
From table name


Cheers
Ashwin
Back to top
View user's profile Send private message
saraswathis

New User


Joined: 06 Sep 2006
Posts: 8

PostPosted: Thu Apr 26, 2007 12:51 pm    Post subject:
Reply with quote

Sir,

but i need the string to be separated using spaces. As the names in the column can be different we cannot use the length to splt the last name and first name using the length parameter. the other names in the same column can be as follows:
DENISE A WHITE
CECILIA B HUGHES
BETTY ARLENE HODGES

how can this be solved?

thks in adv.
Back to top
View user's profile Send private message
ashwinreddy

Active User


Joined: 16 Sep 2004
Posts: 106
Location: Hyderabad

PostPosted: Thu Apr 26, 2007 1:16 pm    Post subject:
Reply with quote

Hi,

If thats the case then before spliting the names we need to detect the spaces between the names.

To do that we can use a Case function but it doesn't look good solution.

Can it be possible through some sub quires, lets hope for a better solution from other members.

Cheers
Ashwin
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10254
Location: italy

PostPosted: Thu Apr 26, 2007 1:56 pm    Post subject: Re: How to split a string in sql query
Reply with quote

You might try to use the POSITION construct to parse the column,
if :
there is only a single blank between words
there is always the same number of words

things might get too complicated for a variable occurrence of those things

<picking on>
since it looks like a flaw in the analysis stage of the project You might want to reconsider some changes to the application structure
... splitting the column in three columns
... adding tree columns with the parsed data
... if the overall architecture allows it consider the use of an "EDITPROC"
<picking off>
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Apr 26, 2007 7:15 pm    Post subject:
Reply with quote

Hello,

You also need to be prepared for names like
JOHN RB SMITH
MARY JENKINS MD
FRED J THOMPSON PHD
MIKE KUMAR JR
and on and on and . . .

From the small set of examples, you see that the names can have many different formats. If you look thru a larger "directory" of the names you need to work with, you may discover most of the formats.

While your request appears rather straightforward, it probably will not be due the variety of ways names are stored.

Is this query going to be inside a program? If it is, (once the rules of the name field construction are known), i believe you will be able to do what you want better in code rather than in a query.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts Replacing same string with different ... vickey_dw DFSORT/ICETOOL 6 Wed Feb 22, 2017 10:44 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us