CODEWITHSUNDEEP
sqloracle-database
mcha
mcha

Reputation: 2998

SQL - Handling String

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!

Upvotes: 1

Views: 366

Answers (4)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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.

Upvotes: 2

JonVD
JonVD

Reputation: 4268

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.

Upvotes: 4

Ahmad Farid
Ahmad Farid

Reputation: 14764

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

Upvotes: 0

Timbo
Timbo

Reputation: 4533

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.

Upvotes: 2

Related Questions