oracle PL/SQL 字符串任务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4866220/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL string tasks
提问by kaibuki
I need to work on two tasks in PL/SQL:
我需要在 PL/SQL 中处理两个任务：
Is to display the first characters of each word in the string e.g. "Standard Query Language", so it should return value as "SQL".
converting text "apple" to upper caps "APPLE", but not using the upper case or lower case predefined functions.
是显示字符串中每个单词的第一个字符，例如“标准查询语言”，因此它应该返回值为“SQL”。
将文本“apple”转换为大写字母“APPLE”，但不使用大写或小写预定义函数。
is it possible in PL/SQL, by not using advanced level functions but just beginner level approach.
在 PL/SQL 中是否可以通过不使用高级功能而仅使用初学者级别的方法。
回答by tbone
Yes, many things are possible in PL/SQL, but in general if you can do it in SQL then don't reinvent built-in functionality using a procedural approach (regex as OMG Ponies commented would probably be a good approach here).
是的，在 PL/SQL 中有很多事情是可能的，但总的来说，如果你可以在 SQL 中做到这一点，那么不要使用过程方法重新发明内置功能（OMG Ponies 评论的正则表达式在这里可能是一个很好的方法）。
That said, if you reallywanted to iterate over a string char by char, you can probably do something like:
也就是说，如果您真的想逐个字符地遍历字符串，您可能可以执行以下操作：
declare
in_string varchar2(20) := 'Im a string';
in_length number;
cnt number := 0;
in_char char(1);
begin
in_length := length(in_string);
while (cnt < in_length)
loop
cnt := cnt + 1;
in_char := substr(in_string, cnt, 1);
-- do something wonderful here
end loop;
exception
when others then raise;
end;
But again, don't do this to uppercase a string (for example) if you can just use the UPPER function ;)
但同样，如果您只能使用 UPPER 函数，请不要这样做来大写字符串（例如）；)
Hope this helps.
希望这可以帮助。
回答by Gary Myers
For (1), I would INITCAP
对于 (1)，我会 INITCAP
select initcap (lower('Structured query Language'))
from dual
Then iterate through the string looking for those uppercase letters.
然后遍历字符串寻找那些大写字母。
回答by redcayuga
Regular Expressions to the rescue!
正则表达式来拯救！
To extract the first character of each word:
提取每个单词的第一个字符：
select regexp_replace ('Structured Query Language', ' \*(.)[^ ]\*', '' ) from dual;
回答by KIRAN MURTHY
Is to display the first characters of each word in the string e.g. "Standard Query Language", so it should return value as "SQL".
DECLARE STR VARCHAR(100):='Standard Query Language'; J NUMBER; BEGIN FOR I IN 1..LENGTH(STR) LOOP IF SUBSTR(STR,I,1)=' ' THEN J:=I+1; DBMS_OUTPUT.PUT(SUBSTR(STR,J,1)); END IF; IF I=1 THEN DBMS_OUTPUT.PUT(SUBSTR(STR,I,1)); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(' '); END;converting text "apple" to upper caps "APPLE", but not using the upper case or lower case predefined functions.
DECLARE STR VARCHAR2(10):='kiran'; BEGIN FOR I IN 1..LENGTH(STR) LOOP DBMS_OUTPUT.PUT(CHR(ASCII(SUBSTR(STR,I,1))-32)); END LOOP; DBMS_OUTPUT.PUT_LINE(' '); END;
是显示字符串中每个单词的第一个字符，例如“标准查询语言”，因此它应该返回值为“SQL”。
DECLARE STR VARCHAR(100):='Standard Query Language'; J NUMBER; BEGIN FOR I IN 1..LENGTH(STR) LOOP IF SUBSTR(STR,I,1)=' ' THEN J:=I+1; DBMS_OUTPUT.PUT(SUBSTR(STR,J,1)); END IF; IF I=1 THEN DBMS_OUTPUT.PUT(SUBSTR(STR,I,1)); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(' '); END;将文本“apple”转换为大写字母“APPLE”，但不使用大写或小写预定义函数。
DECLARE STR VARCHAR2(10):='kiran'; BEGIN FOR I IN 1..LENGTH(STR) LOOP DBMS_OUTPUT.PUT(CHR(ASCII(SUBSTR(STR,I,1))-32)); END LOOP; DBMS_OUTPUT.PUT_LINE(' '); END;
回答by kaibuki
I have written the code for performing the both tasks mentioned above, they work fine, haven't added exception handling or validation.
我已经编写了用于执行上述两个任务的代码，它们工作正常，没有添加异常处理或验证。
(1) DECLARE
NAME VARCHAR2(100):='&NAM';
CURR_CHAR VARCHAR2(1);
ABBRV VARCHAR2(100);
BEGIN
ABBRV := SUBSTR(NAME,1,1);
FOR I IN 1..LENGTH(NAME) LOOP
CURR_CHAR := SUBSTR(NAME,I,1);
IF CURR_CHAR = CHR(32) THEN
-- &PL('SPACE');
ABBRV := ABBRV || SUBSTR(NAME,I+1,1);
END IF;
END LOOP;
&PL('ABBRIVATION OF : ' || NAME);
&PL(' IS ... : ' || ABBRV);
END;
(2)DECLARE
VALUE VARCHAR2(100) := '&VAL';
CURR_CHAR VARCHAR2(1);
UPPER_CASE VARCHAR2(100);
BEGIN
FOR I IN 1..LENGTH(VALUE) LOOP
CURR_CHAR := SUBSTR(VALUE,I,1);
UPPER_CASE := UPPER_CASE || CHR(ASCII(CURR_CHAR)-32);
END LOOP;
&PL(' UPPER CASE : ' || UPPER_CASE);
END;
