MIHIR KAR
asked on
String Validation using SQL!
Hi Expert,
How to convert the input string to given O/P? using Oracle Sql,
Table : Name
ENAME_iNPUT
--------------------
kalia
kalia jag
kalia jag jagdish
kalia jag jagdish acharya
Expected O/P-
-------------------
kali
kaja
kajj
kjja
It display according to words if one word then it display 1st 4 character and 2 word then display 2 char from 1st word and 2nd char from 2nd word and so on
Please share the select statement how to achieve this using SQL?
Thanks.
How to convert the input string to given O/P? using Oracle Sql,
Table : Name
ENAME_iNPUT
--------------------
kalia
kalia jag
kalia jag jagdish
kalia jag jagdish acharya
Expected O/P-
-------------------
kali
kaja
kajj
kjja
It display according to words if one word then it display 1st 4 character and 2 word then display 2 char from 1st word and 2nd char from 2nd word and so on
Please share the select statement how to achieve this using SQL?
Thanks.
Use instring to find occurrence of spaces, substring to snip out, case statement for these for your cases.
your description of intended output does not match the output you have shown.
Is this a learning exercise or actual production requirement?
What if there are 5, 6, or 20 words?
What if there are 5, 6, or 20 words?
Assuming your printed output is correct and your description is wrong then try something like this...
SELECT ename_input,
LISTAGG(SUBSTR(str, 1, CASE WHEN cnt = 1 THEN 4 WHEN cnt = 2 THEN 2 WHEN cnt > 2 AND rn = 1 THEN 2 ELSE 1 END))
WITHIN GROUP (ORDER BY rn)
FROM (SELECT ename_input,
str,
cnt,
ROW_NUMBER() OVER(PARTITION BY ename_input ORDER BY rn) rn
FROM (SELECT ename_input,
COLUMN_VALUE str,
ROWNUM rn,
COUNT(*) OVER (PARTITION BY ename_input) cnt
FROM name, TABLE(str2tbl(REGEXP_REPLA CE(ename_i nput, ' +', ' '), ' '))) x)
GROUP BY ename_input;
str2tbl is function you will need to create if you don't already ahve it
you can search for it on google or EE
there are lots of examples
SELECT ename_input,
LISTAGG(SUBSTR(str, 1, CASE WHEN cnt = 1 THEN 4 WHEN cnt = 2 THEN 2 WHEN cnt > 2 AND rn = 1 THEN 2 ELSE 1 END))
WITHIN GROUP (ORDER BY rn)
FROM (SELECT ename_input,
str,
cnt,
ROW_NUMBER() OVER(PARTITION BY ename_input ORDER BY rn) rn
FROM (SELECT ename_input,
COLUMN_VALUE str,
ROWNUM rn,
COUNT(*) OVER (PARTITION BY ename_input) cnt
FROM name, TABLE(str2tbl(REGEXP_REPLA
GROUP BY ename_input;
str2tbl is function you will need to create if you don't already ahve it
you can search for it on google or EE
there are lots of examples
I was thinking the CASE looked off.
4 words doesn't produce expected output: kajja instead of kjja
4 words doesn't produce expected output: kajja instead of kjja
ASKER
Hi @sdstuber I have created the function "str2tab" and the o/p looks little differ .
But here the requirement is if there are more than 4 word then it should pick only the 1st letter of each word.
Is this a learning exercise or actual production requirement?
Hi @slightwv this is required for production implementation.
Thanks
But here the requirement is if there are more than 4 word then it should pick only the 1st letter of each word.
SELECT name_input,
LISTAGG(SUBSTR(str, 1, CASE WHEN cnt = 1 THEN 4 WHEN cnt = 2 THEN 2 WHEN cnt > 2 AND rn = 1 THEN 2 ELSE 1 END))
WITHIN GROUP (ORDER BY rn)
FROM (SELECT name_input,
str,
cnt,
ROW_NUMBER() OVER(PARTITION BY name_input ORDER BY rn) rn
FROM (SELECT name_input,
COLUMN_VALUE str,
ROWNUM rn,
COUNT(*) OVER (PARTITION BY name_input) cnt
FROM string_tab, TABLE(str2tbl(REGEXP_REPLACE(name_input, ' +', ' '), ' '))) x)
GROUP BY name_input;
O/P
-----
kali
kaja
kajj
kajja -- Here it picked 1st 2 letter
Is this a learning exercise or actual production requirement?
Hi @slightwv this is required for production implementation.
Thanks
>>>> kajja -- Here it picked 1st 2 letter
maybe it didn't, it just looks like it did.
what are the inputs for those outputs?
"kalia jag jagdish acharya" > kajja, 2 letters from the first, 1st letter from each of the next 3 words. Just because the 3rd word happens to have "ja" at the beginning doesn't mean the code picked 2 letters, the 4th word begins with "a"
for more than 4 words, do you want the 1st letter of all words, including the first, or 2 from the first and 1 from every word thereafter?
If it's 2 from the 1st and 1 for everything thereafter, what I posted already does that.
If it's 1 from every word, then simply change
WHEN cnt > 2
to
WHEN cnt <=4
maybe it didn't, it just looks like it did.
what are the inputs for those outputs?
"kalia jag jagdish acharya" > kajja, 2 letters from the first, 1st letter from each of the next 3 words. Just because the 3rd word happens to have "ja" at the beginning doesn't mean the code picked 2 letters, the 4th word begins with "a"
for more than 4 words, do you want the 1st letter of all words, including the first, or 2 from the first and 1 from every word thereafter?
If it's 2 from the 1st and 1 for everything thereafter, what I posted already does that.
If it's 1 from every word, then simply change
WHEN cnt > 2
to
WHEN cnt <=4
ASKER
For more than 4 words, do you want the 1st letter of all words, including the first, or 2 from the first and 1 from every word thereafter?
If there are 4 or more than 4 words then it should pick only the 1st letter of the word and so on ...
Thanks.
If there are 4 or more than 4 words then it should pick only the 1st letter of the word and so on ...
"kalia jag jagdish acharya" -- kjja
"kalia jay jagdish acharya hello" -- kjjah
"kalia jay jagdish acharya hello how" -- kjjahh
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Thanks , This is accurate as expected o/p.