Hey Singh,
I love it! Outstanding! Have you been using Anthony Molinaro('you
developer you") book (SQL Cookbook)? Be honest! Speak the truth!
Hehe.
-----Original Message-----
From: Singh Rahul
[mailto:db2udbdba-ezmlmshield-x67195232.[Email address protected]
Sent: Wednesday, September 27, 2006 8:31 AM
To: LazyDBA Discussion
Subject: RE: Find the position of a character in a string
Hey Ed ,
A try with recursive sql
WITH t(lastname,number, pos) AS
( select lastname,0,0 from employee where lastname like '%A%'
UNION ALL
SELECT t.lastname,number+1, LOCATE('A', t.lastname, pos+1)
FROM t
WHERE number <100
) SELECT distinct lastname,pos FROM t where pos <> 0
order by 1,2;
regards,
Rahul Singh
-----Original Message-----
From: Edwards Ed [mailto:db2udbdba-ezmlmshield-x42263762.[Email address
protected]
Sent: Wednesday, September 27, 2006 5:10 PM
To: LazyDBA Discussion
Subject: RE: Find the position of a character in a string
To All,
Below is another example of counting the number of times a character
appears in a string (field or column).
SELECT LASTNAME,
ABS(LENGTH(LASTNAME) -LENGTH(REPLACE(LASTNAME,'A','AA')) /LENGTH(','))
COUNT_A
FROM EMPLOYEE
WHERE LASTNAME LIKE '%A%'
ORDER BY 1;
-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x63637684.[Email address protected]
Sent: Wednesday, September 27, 2006 7:23 AM
To: LazyDBA Discussion
Subject: RE: Find the position of a character in a string
To All,
Below is another example of finding the position(s) of a character in
a string (field or column).
SELECT LASTNAME, POSITION
FROM
(SELECT LASTNAME, ITER.POSITION, SUBSTR(LASTNAME, ITER.POSITION, 1) C
FROM EMPLOYEE,
(SELECT COLNO AS POSITION
FROM SYSCAT.COLUMNS WHERE TABSCHEMA = 'SYSIBM'
AND TABNAME = 'SYSCOLUMNS' AND COLNO >= 1) ITER
WHERE ITER.POSITION <= LENGTH(LASTNAME)) X
WHERE C = 'A'
ORDER BY 1,2;
-----Original Message-----
From: Edwards Ed [mailto:db2udbdba-ezmlmshield-x45679882.[Email address
protected]
Sent: Tuesday, September 26, 2006 11:19 PM
To: LazyDBA Discussion
Subject: RE: Find the position of a character in a string
Well, well, well!
It's Ed Edwards from DOE: DB2 UDB and Z/OS, OS/390 Glue Root #1!
hehe. Am I back? Can you hear me now? Hehe.
-----Original Message-----
From: Schleicher Jeremy D.
[mailto:db2udbdba-ezmlmshield-x37129598.[Email address protected]
Sent: Monday, September 25, 2006 3:55 PM
To: LazyDBA Discussion
Subject: FW: Find the position of a character in a string
SELECT LASTNAME,
CASE SUBSTR(lastNAME,1,1) WHEN 'A' THEN '1' END AS "POS1",
CASE SUBSTR(lastNAME,2,1) WHEN 'A' THEN '2' END AS "POS2",
CASE SUBSTR(lastNAME,3,1) WHEN 'A' THEN '3' END AS "POS3",
CASE SUBSTR(lastNAME,4,1) WHEN 'A' THEN '4' END AS "POS4",
CASE SUBSTR(lastNAME,5,1) WHEN 'A' THEN '5' END AS "POS5",
CASE SUBSTR(lastNAME,6,1) WHEN 'A' THEN '6' END AS "POS6",
CASE SUBSTR(lastNAME,7,1) WHEN 'A' THEN '7' END AS "POS7",
CASE SUBSTR(lastNAME,8,1) WHEN 'A' THEN '8' END AS "POS8",
CASE SUBSTR(lastNAME,9,1) WHEN 'A' THEN '9' END AS "POS9",
CASE SUBSTR(lastNAME,10,1) WHEN 'A' THEN '10' END AS "POS10"
FROM EMPLOYEE
WHERE LASTNAME LIKE '%A%'
ORDER BY LASTNAME
FETCH FIRST 100 ROWS ONLY;
Please take a few minutes to provide feedback on the quality of service
you received. The Department of Education values your feedback as a
customer. Commissioner John L. Winn is committed to continuously
assessing and improving the level and quality of services provided to
you by Department staff. Simply use the link below. Thank you in
advance for completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
%20Mon%2025%20Sep%202006
-----Original Message-----
From: David Baker
[mailto:db2udbdba-ezmlmshield-x41083109.[Email address protected]
Sent: Monday, September 25, 2006 1:07 PM
To: LazyDBA Discussion
Subject: Find the position of a character in a string
Anyone know how to find the position of a character (or positions if
more
than one in a string?
Ex: find ';' in kji;23ijij;kjifd would return 4 and 11.
Thanks!
David Baker
Taido Ryu Jujitsu
www.TaidoRyu.com
518-210-1000
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page