Changing name formats with functions.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Changing name formats with functions.

Author

Topic

e1esdee
Starting Member

3 Posts

Posted - 2011-05-11 : 20:40:18

I'm a little new to SQL so please bare with me if my terminology or other things aren't phrased exactly as they should be. I have to create a function that formats names from a table in the following manner; if they have a middle initial John P. Smith, if they don't have a middle initial John Smith, and exceptions as to why the function doesn't execute if applicable. What kind of syntax would I use to accomplish this?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-11 : 20:52:09

Would something like this example work for you? I didn't quite understand what you meant by "exceptions as to why the function doesn't execute".


CREATE TABLE #tmp (firstname VARCHAR(32), lastname VARCHAR(32), middleinitial VARCHAR(1));
INSERT INTO #tmp VALUES ('John','Smith',NULL);
INSERT INTO #tmp VALUES ('Sunita','Beck', '');
INSERT INTO #tmp VALUES ('Jane','Doe','W');

SELECT
   firstname + ' ' + ISNULL(nullif(middleinitial,'')+'.','') + lastname
FROM #tmp;

DROP TABLE #tmp;

If this will do it, easy enough to make that into a function, but it is so short that you may even want to just keep it in-line.

e1esdee
Starting Member

3 Posts

Posted - 2011-05-11 : 20:57:31

BEGIN
SELECT
employee.f_name,
employee.initial,
employee.l_name
FROM hartmar.employee
RETURN
EXCEPTION
when no_data_found then
dbms_output.put_line('Invalid name');
when others then
dbms_output.put_line(SQLERROR);
END;
/

http://i988.photobucket.com/albums/af10/e1esdee/ERD.png

I'm kind of working from the bottom up at the moment but that's what I have so far. I attached a link to the tables that I'm using. I have to make it so that employee.f_name, employee.initial, and employee.l_name display as John P. Smith or just John Smith if they don't have a middle initial.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-11 : 21:06:04

This forum is specifically for Microsoft SQL, so there would very few people here who are familiar with Oracle. You may get faster and better responses at sites such as dbforums.com.

e1esdee
Starting Member

3 Posts

Posted - 2011-05-11 : 21:14:18

Ah, alright thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-12 : 02:59:55

You can simulate what sunita posted or Post your question at www.orafaq.com

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources