SQL - Handling String

Question

for example, i have the string 'This Is An Example Of The String'
and i want to return this result 'This is an Example of the String'
==> I want all the 'Is', 'An' 'Of' and 'The' in lower cases, the rest should stay in Initcap.

How can be this done in a simple and unique query? here is my query to lower case only the 'Of' :

SELECT 'This Is An Example Of The String',
       CASE
         WHEN 'This Is An Example Of The String' like '% Of %'
         THEN replace('This Is An Example Of The String', ' Of ', ' of ')
       END
  FROM dual ;

Thanks!

This could probably be done using regexp_replace() but I have never used it with regex groups (which would be needed in this case) — user330315
– user330315, Commented Nov 26, 2010 at 14:36

JonVD · Accepted Answer · 2010-11-26 15:43:36Z

4

Try this:

SELECT REPLACE(REPLACE(REPLACE(REPLACE('This Is An Example Of The String', 'Of', 'of'), 'The', 'the'), 'An', 'an'), 'Is', 'is')    FROM dual;

I feel a little dirty after writing that though.

Edit: Removed the extra 'an' replace, then added indentation, then removed indentation again. It looks ugly no matter how you wrap it.

edited Nov 26, 2010 at 15:43

answered Nov 26, 2010 at 14:30

JonVD

4,27826 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JonVD Over a year ago

Sometimes little evils are necessary =) Doesn't mean I have to like them.

mcha Over a year ago

Thanks! and i agree, its not bautiful ;)

JonVD Over a year ago

Really the badger has it correct, this sort of thing is far easier and cleaner in your business logic, but I also understand somethings cannot be easily changed. This is especially true for large-scale or live applications.

Jeffrey Kemp Over a year ago

Needs more spaces. "Their Thesaurus Anticipated Theo's Offer". Also, why the double An,an,The,the,An,an,The,the?

JonVD Over a year ago

The double an/the issue was on account of me not paying attention. Make no mistake, this is a quick and dirty, direct solution to the OP's problem. As I said in a previous comment, really badger highlighted the correct way to deal with this.

Timbo · Accepted Answer · 2010-11-26 14:31:55Z

2

Basically it's going to take a lot of logic of the nature you've described. There's no quick and easy way. You'd find it quicker and easier to do this type of manipulation in your business logic code rather than at the database.

If you are going to do it in the database consider wrapping up the logic in a function, like this one.

answered Nov 26, 2010 at 14:31

Timbo

4,5532 gold badges29 silver badges29 bronze badges

2 Comments

mcha Over a year ago

Thanks for your answer, the problem is they are already INITCAP(column_name) in the databaseand i need to update them

Timbo Over a year ago

Understood. Just thinking in terms of options like splitting up the strings and manipulating them against dictionaries of other values that's a lot easier to do in most things other than SQL! Good luck.

Bob Jarvis - Слава Україні · Accepted Answer · 2010-11-26 19:54:58Z

Although it's not a pure SQL solution, another option would be to define a function which transformed the string as desired, perhaps calling it REPLACE_MULTI. The invocation would be something like

SELECT REPLACE_MULTI('This Is An Example Of The String',
                     'Is|An|Of|The',
                     'is|an|of|the')
  FROM DUAL;

and the implementation would be something along the lines of

CREATE OR REPLACE FUNCTION REPLACE_MULTI(strOriginal           IN VARCHAR2,
                                         strTokens_to_replace  IN VARCHAR2,
                                         strReplacement_tokens IN VARCHAR2)
  RETURN VARCHAR2
IS
  strResult             VARCHAR2(2000);
  arrTokens_to_replace  DBMS_SQL.VARCHAR2A;
  arrReplacement_tokens DBMS_SQL.VARCHAR2A;
  i                     NUMBER;

  FUNCTION extract_tokens(p_string IN VARCHAR2,
                          p_separators IN VARCHAR2) RETURN DBMS_SQL.VARCHAR2A
  IS
    arrTokens DBMS_SQL.VARCHAR2A;
  BEGIN
    WITH sel_string AS 
        (SELECT p_string AS fullstring FROM DUAL)
    SELECT SUBSTR(fullstring, beg + 1, end_p - beg - 1) AS token
      BULK COLLECT INTO arrTokens
      FROM (SELECT beg, LEAD(beg) OVER (ORDER BY beg) AS end_p, fullstring
              FROM (SELECT beg, fullstring
                      FROM (SELECT LEVEL beg, fullstring
                              FROM sel_string
                              CONNECT BY LEVEL <= LENGTH(fullstring))
                      WHERE INSTR(p_separators, SUBSTR(fullstring, beg, 1)) > 0
                    UNION ALL
                      SELECT 0, fullstring FROM sel_string
                    UNION ALL
                      SELECT LENGTH(fullstring) + 1, fullstring FROM sel_string))
      WHERE end_p IS NOT NULL AND
            end_p > beg + 1;

    RETURN arrTokens;
  END extract_tokens;

BEGIN
  arrTokens_to_replace := extract_tokens(strTokens_to_replace, '|');
  arrReplacement_tokens := extract_tokens(strReplacement_tokens, '|');

  strResult := strOriginal;

  FOR i IN 1..arrTokens_to_replace.COUNT LOOP
    strResult := REGEXP_REPLACE(strResult,
                                '^' || arrTokens_to_replace(i) || ' ', 
                                arrReplacement_tokens(i));
    strResult := REPLACE(strResult,
                         ' ' || arrTokens_to_replace(i) || ' ',
                         ' ' || arrTokens_to_replace(i) || ' ');
    strResult := REGEXP_REPLACE(strResult,
                                ' ' || arrTokens_to_replace(i) || '$',
                                ' ' || arrReplacement_tokens(i));
  END LOOP;

  RETURN strResult;
END REPLACE_MULTI;

I'm sure that there are token strings which can be created which will break the regular expression-based parsing (try putting a '^' or '$' in there and watch the sparks fly :-) but this is good enough for an initial hack.

(Incidentally, the 'extract_tokens' routine isn't mine - I found it on the web somewhere a while back and am eternally grateful to whoever it was that created this).

Share and enjoy.

Ahmad Farid · Accepted Answer · 2010-11-26 14:33:29Z

0

I can tell you the algorithm but I am not sure how to do that in SQL. for example:

words[] = string.split(" ")
foreach word in words
---if(word.length<=3) //do that only for short words
-------if(word[i]>=65 && word[i]<=90) //check the ascii code for upper case
------------word[i] += 21; // transfer it into lower case
---new sentence += " " + words[i] //add to the resultant string

answered Nov 26, 2010 at 14:33

Ahmad Farid

14.8k45 gold badges99 silver badges138 bronze badges

Collectives™ on Stack Overflow

SQL - Handling String

4 Answers 4

5 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related