| concatenate text in the same field [message #372570] |
Fri, 23 February 2001 09:34  |
Sharon
Messages: 10
Registered: February 2001
|
Junior Member |
|
|
I am new to sql so bear with me here....
This is what I need to do:
Field contains business names which can be 1 or more words.
Example: Jones Plumbing or American Land Title Association
I want to do one of two things to each business name in this column.
Either Condense the name to one word if it is two words or less or create an acronym if it is 3 words or more.
Example: JonesPlumbing or ALTA
Am I correct in thinking I can concatenate Jones Plumbing in this fashion:
SELECT REPLACE('ab fgh,' ','')
Result being abfgh
will the space in the second expression be recognized or will it cause an error? Instead of using an actual name, could I use a var that passes the contents of the field to the first expression and then performs this action?
As far as creating acronyms goes... I don't have a clue where to start.
doing a word count will give me the ability to do an ifthen statement to direct the function to concatenate the names or acronym it. How do I get it to pull only the first letter of each word?
Using Mid() I would have to know the count of all the letters in each field and this is impossible since I have over 1500 fields in the column.
Thanks much for your help
Sharon
|
|
|
|
| Re: concatenate text in the same field [message #372573 is a reply to message #372570] |
Fri, 23 February 2001 14:29  |
amarpatgiri
Messages: 11
Registered: December 2000
|
Junior Member |
|
|
Use a function like the following to get the acronym:
CREATE OR REPLACE FUNCTION acronym ( str VARCHAR2 )
RETURN VARCHAR2 IS
i PLS_INTEGER;
ch CHAR(1);
acronym VARCHAR2(20);
addIt BOOLEAN;
BEGIN
IF LENGTH(str) IS NULL THEN
RETURN '';
END IF;
acronym := SUBSTR(str,1,1);
addIt := FALSE;
FOR i IN 1..LENGTH(str) LOOP
ch := SUBSTR(str,i,1);
IF addIt THEN
acronym := acronym || ch;
END IF;
IF ch = ' ' THEN
addIt := TRUE;
ELSE
addIt := FALSE;
END IF;
END LOOP;
RETURN acronym;
END;
-amar
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
