| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-05-11, 08:50
|
|
Registered User
|
|
Join Date: Oct 2011
Posts: 2
|
|
|
DB2 split alphanumeric values
|
|
I have the values in a column as:
'asdf1230'
,'11134asd'
,ssd112wwe'
I want to split the string as the prefix of characters and suffix of characters and insert into another table using DB2 queries(see below):
result should be:
asdf 1230 <blank>
<blank> 11134 asd
ssd 112 wwe
|
|
10-05-11, 09:21
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
An example:
Code:
------------------------------ Commands Entered ------------------------------
WITH
test_data(in_string) AS (
VALUES
'asdf1230'
, '11134asd'
, 'ssd112wwe'
)
SELECT in_string
, LEFT(in_string , pos_num - 1) AS first_col
, SUBSTR(in_string , pos_num , len_num) AS second_col
, SUBSTR(in_string , pos_num + len_num) AS third_col
FROM (SELECT in_string
, LOCATE( ' ' , TRANSLATE(in_string , ' ' , '0123456789') )
, LENGTH(in_string)
- LENGTH( TRANSLATE(in_string , '' , '0123456789' , '') )
FROM test_data
) q(in_string , pos_num , len_num)
;
------------------------------------------------------------------------------
IN_STRING FIRST_COL SECOND_COL THIRD_COL
--------- --------- ---------- ---------
asdf1230 asdf 1230
11134asd 11134 asd
ssd112wwe ssd 112 wwe
3 record(s) selected.
|
Last edited by tonkuma; 10-05-11 at 09:24.
|
10-05-11, 09:55
|
|
Registered User
|
|
Join Date: Oct 2011
Posts: 2
|
|
|
DB2 split alphanumeric values
|
|
Thank you for your guidence:
Actually my request is like:
I have three columns like
firstcol
secondcol
thirdcol
The values are in the secondcol
'asdf1230'
, '11134asd'
, 'ssd112wwe'
I want to update the firstcol and thirddcol based on the request(previously) on fly and need to leave only numeric in the secondcol.
firstcol secondcol thirdcol
asdf 1230 <blank>
<blank> 11134 asd
ssd 112 wwe
I forgot to add the occurence of the another data:
'awd023s-sd234'
this case should be:
firstcol secondcol thirdcol
awd 023 s-sd234
Regards
Muthu
|
|
10-05-11, 10:33
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Please write your complete requirement at first.
This is not a practice nor homework, I thought.
So, you should NOT show the requirements from simple one first, then add additional requirements step by step.
Anyhow, here is a revised example:
Code:
------------------------------ Commands Entered ------------------------------
WITH
test_data(secondcol) AS (
VALUES
'asdf1230'
, '11134asd'
, 'ssd112wwe'
, 'awd023s-sd234'
)
SELECT secondcol
, LEFT (secondcol , pos_num - 1) AS new_firstcol
, SUBSTR(secondcol , pos_num , len_num) AS new_secondcol
, SUBSTR(secondcol , pos_num + len_num) AS new_thirdcol
FROM (SELECT secondcol
, pos_num
, LENGTH(secondcol)
- pos_num + 1
- LENGTH( LTRIM( SUBSTR(blank_num , pos_num) ) ) AS len_num
FROM (SELECT secondcol
, blank_num
, LOCATE( ' ' , blank_num ) AS pos_num
FROM (SELECT secondcol
, TRANSLATE(secondcol , ' ' , '0123456789') AS blank_num
FROM test_data
) p
) q
) r
;
------------------------------------------------------------------------------
SECONDCOL NEW_FIRSTCOL NEW_SECONDCOL NEW_THIRDCOL
------------- ------------- ------------- -------------
asdf1230 asdf 1230
11134asd 11134 asd
ssd112wwe ssd 112 wwe
awd023s-sd234 awd 023 s-sd234
4 record(s) selected.
The example of update statement might be:
Code:
/* Not tested. */
UPDATE <your table>
SET (firstcol , secondcol , thirdcol)
= (/* Put the query example with removing secondcol. */
SELECT LEFT (secondcol , pos_num - 1)
, SUBSTR(secondcol , pos_num , len_num)
, SUBSTR(secondcol , pos_num + len_num)
FROM LATERAL
(SELECT pos_num
, LENGTH(secondcol)
- pos_num + 1
- LENGTH( LTRIM( SUBSTR(blank_num , pos_num) ) ) AS len_num
FROM LATERAL
(SELECT blank_num
, LOCATE( ' ' , blank_num ) AS pos_num
FROM LATERAL
(VALUES TRANSLATE(secondcol , ' ' , '0123456789')
) p(blank_num)
) q
) r
)
;
|
|
10-05-11, 11:01
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
This example may be easier to read by removing nesting of subqueries(nested-table-expressions).
Code:
UPDATE <your table>
SET (firstcol , secondcol , thirdcol)
= (/* Put the query example. */
SELECT LEFT (secondcol , pos_num - 1)
, SUBSTR(secondcol , pos_num , len_num)
, SUBSTR(secondcol , pos_num + len_num)
FROM LATERAL
(VALUES TRANSLATE(secondcol , ' ' , '0123456789')
) p(blank_num)
, LATERAL
(VALUES LOCATE( ' ' , blank_num )
) q(pos_num)
, LATERAL
(VALUES LENGTH(secondcol)
- pos_num + 1
- LENGTH( LTRIM( SUBSTR(blank_num , pos_num) ) )
) r(len_num)
)
;
|
|
10-05-11, 11:29
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Here is a test result of the query(row-fullselect) inside the last update example.
Code:
------------------------------ Commands Entered ------------------------------
WITH
test_data(secondcol) AS (
VALUES
'asdf1230'
, '11134asd'
, 'ssd112wwe'
, 'awd023s-sd234'
)
SELECT *
FROM (SELECT secondcol
, LEFT (secondcol , pos_num - 1)
, SUBSTR(secondcol , pos_num , len_num)
, SUBSTR(secondcol , pos_num + len_num)
FROM test_data
, LATERAL
(VALUES TRANSLATE(secondcol , ' ' , '0123456789')
) p(blank_num)
, LATERAL
(VALUES LOCATE( ' ' , blank_num )
) q(pos_num)
, LATERAL
(VALUES LENGTH(secondcol)
- pos_num + 1
- LENGTH( LTRIM( SUBSTR(blank_num , pos_num) ) )
) r(len_num)
)
;
------------------------------------------------------------------------------
SECONDCOL 2 3 4
------------- ------------- ------------- -------------
asdf1230 asdf 1230
11134asd 11134 asd
ssd112wwe ssd 112 wwe
awd023s-sd234 awd 023 s-sd234
4 record(s) selected.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
DB2 split alphanumeric values
