We help IT Professionals succeed at work.

String Validation using SQL!

94 Views
Last Modified: 2018-08-25
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.
Comment
Watch Question

jtriftsMI and Automation
CERTIFIED EXPERT

Commented:
Use instring to find occurrence of spaces, substring to snip out, case statement for these for your cases.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
your description of intended output does not match the output you have shown.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Is this a learning exercise or actual production requirement?

What if there are 5, 6, or 20 words?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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_REPLACE(ename_input, ' +', ' '), ' '))) 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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I was thinking the CASE looked off.

4 words doesn't produce expected output:  kajja instead of kjja
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginner

Author

Commented:
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.

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 

Open in new window


Is this a learning exercise or actual production requirement?

Hi @slightwv this is required for production implementation.


Thanks
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>>>  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
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginner

Author

Commented:
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 ...

"kalia jag jagdish acharya"                      --  kjja
"kalia jay jagdish acharya hello"             -- kjjah
"kalia jay jagdish acharya hello how"    -- kjjahh

Open in new window


Thanks.
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginner

Author

Commented:
Perfect! Thanks , This is accurate as expected o/p.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.