Dynamically Generate FullName in SELECT stmt

Dynamically Generate FullName in SELECT stmt

Post by Ed Sin » Sun, 31 Dec 2000 09:27:52



Hi,

I'm trying to create a SELECT statement that returns all of the
columns in a table as well as another field that does not exist in the
table - the full name, generated from the fields in the DB (name
prefix, first name, middle name, last name).  It is relatively simple
to simply concatenate the fields together, but to do some conditional
work it another problem.

This is all in a Stored Procedure, so I can declare a variable and
work with that if applicable...

SELECT *, FirstName + LastName as FullName FROM myTable WHERE
myTableID = 10

I would like to put in some conditional statements in there to allow
the proper number of spaces.

if (NamePrefix <> '') then
        FullName = FullName + NamePrefix
end if
if (FirstName <> '') then
        if (FullName <> "") then
                FullName = FullName + ' '
        end if
        FullName = FullName + FirstName
end if
etc.....

What I am trying to avoid is if there is only a Last Name, I don't
want a few spaces before and after the Full Name.

I can't seem to figure out how to do this.

Also, as an aside, is the SQL FAQ page still functional?  I've
misplaced the URL.  Could someone tell it to me?

Thanks in advance.

--Ed

 
 
 

Dynamically Generate FullName in SELECT stmt

Post by Uwe Ricke » Sun, 31 Dec 2000 09:31:55


Hi Ed,

you do not have to check, if Middlename or lastname or any other field is
empty, if you use LTRIM-Function

e.g

SELECT
    LTRIM(FirstName + ' ') + LTRIM(MiddleName + ' ') + LTRIM(LastName + ' ')
    FROM yourTable
WHERE
    ....

Best regards from germany and a happy new year to all...

--
Gru?, Uwe Ricken
MCP FOR MS ACCESS DEVELOPMENT
exklusiver Ausbilder der "Ausbilder und Berufschullehrer des Landes Hessen"
fr Datenbankentwicklung
GNS GmbH, Frankfurt am Main
http://www.gns-online.de

____________________________________________________
APP:    http://www.AccessProfiPool.com
FAQ:   http://www.donkarl.com/AccessFAQ.htm
____________________________________________________



Quote:> Hi,

> I'm trying to create a SELECT statement that returns all of the
> columns in a table as well as another field that does not exist in the
> table - the full name, generated from the fields in the DB (name
> prefix, first name, middle name, last name).  It is relatively simple
> to simply concatenate the fields together, but to do some conditional
> work it another problem.

> This is all in a Stored Procedure, so I can declare a variable and
> work with that if applicable...

> SELECT *, FirstName + LastName as FullName FROM myTable WHERE
> myTableID = 10

> I would like to put in some conditional statements in there to allow
> the proper number of spaces.

> if (NamePrefix <> '') then
> FullName = FullName + NamePrefix
> end if
> if (FirstName <> '') then
> if (FullName <> "") then
> FullName = FullName + ' '
> end if
> FullName = FullName + FirstName
> end if
> etc.....

> What I am trying to avoid is if there is only a Last Name, I don't
> want a few spaces before and after the Full Name.

> I can't seem to figure out how to do this.

> Also, as an aside, is the SQL FAQ page still functional?  I've
> misplaced the URL.  Could someone tell it to me?

> Thanks in advance.

> --Ed


 
 
 

Dynamically Generate FullName in SELECT stmt

Post by Umachandar Jayachandra » Sun, 31 Dec 2000 09:50:03


    See below. I assume the format "Prefix FirstName MiddleName LastName".
You can do the NULLIF check on the firstname, middlename & lastname columns
also to ignore empty names. What are doing is essentially writing a series
of CASE expression but using the compact COALESCE format instead. The
expression below will handle NULLs in the columns too!

COALESCE( NULLIF( t.Prefix , '' ) + ' ' , '' ) +
COALESCE( t.FirstName + ' ' + t.MiddleName + ' ' + t.LastName ,
          t.FirstName + ' ' + t.MiddleName ,
          t.FirstName + ' ' + t.LastName ,
          t.MiddleName + ' ' + t.LastName ,
          t.FirstName ,
          t.MiddleName ,
          t.LastName ,
          '' )

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

Dynamically Generate FullName in SELECT stmt

Post by Ed Sin » Sun, 31 Dec 2000 10:12:01


Thanks,

I ended up using the following...

SELECT
        LTRIM(ISNULL(NamePrefix,'') + ' ') +
        LTRIM(ISNULL(FirstName,'') + ' ') +
        LTRIM(ISNULL(MiddleName,'') + ' ') +
        LTRIM(ISNULL(LastName,'') + ' ') +
        LTRIM(ISNULL(NameSuffix,'') + ' ') as FullName, *
FROM myTable
WHERE myTableID = 10

--Ed

On Sat, 30 Dec 2000 01:31:55 +0100, "Uwe Ricken"


>Hi Ed,

>you do not have to check, if Middlename or lastname or any other field is
>empty, if you use LTRIM-Function

>e.g

>SELECT
>    LTRIM(FirstName + ' ') + LTRIM(MiddleName + ' ') + LTRIM(LastName + ' ')
>    FROM yourTable
>WHERE
>    ....

>Best regards from germany and a happy new year to all...

>--
>Gru?, Uwe Ricken
>MCP FOR MS ACCESS DEVELOPMENT
>exklusiver Ausbilder der "Ausbilder und Berufschullehrer des Landes Hessen"
>fr Datenbankentwicklung
>GNS GmbH, Frankfurt am Main
>http://www.gns-online.de

>____________________________________________________
>APP:    http://www.AccessProfiPool.com
>FAQ:   http://www.donkarl.com/AccessFAQ.htm
>____________________________________________________



>> Hi,

>> I'm trying to create a SELECT statement that returns all of the
>> columns in a table as well as another field that does not exist in the
>> table - the full name, generated from the fields in the DB (name
>> prefix, first name, middle name, last name).  It is relatively simple
>> to simply concatenate the fields together, but to do some conditional
>> work it another problem.

>> This is all in a Stored Procedure, so I can declare a variable and
>> work with that if applicable...

>> SELECT *, FirstName + LastName as FullName FROM myTable WHERE
>> myTableID = 10

>> I would like to put in some conditional statements in there to allow
>> the proper number of spaces.

>> if (NamePrefix <> '') then
>> FullName = FullName + NamePrefix
>> end if
>> if (FirstName <> '') then
>> if (FullName <> "") then
>> FullName = FullName + ' '
>> end if
>> FullName = FullName + FirstName
>> end if
>> etc.....

>> What I am trying to avoid is if there is only a Last Name, I don't
>> want a few spaces before and after the Full Name.

>> I can't seem to figure out how to do this.

>> Also, as an aside, is the SQL FAQ page still functional?  I've
>> misplaced the URL.  Could someone tell it to me?

>> Thanks in advance.

>> --Ed

 
 
 

1. T-SQL:Getting cursor on a dynamically built select stmt

I have been working on Oracle for some time
but I am pretty new to MS SQL.

I need to traverse through a dynamically
built SQL.

1. I HAVE to use a cursor for traversing - right?

2. How can I set a  cursor on a dynamically built
    Select string?

3. Even the fetch will have to be into different number of
    variables - any ideas? Can we EXECUTE a string i.e.

    EXECUTE    'fetch from cursx into x1, x2'

4. Is it possible to assign the result set of an
    sp_executesql to a cursor?

Basically I am trying to create a common procedure
which will write the Old values and the new values
of all the columns of a table into an audit table.

I had used dbms_sql, dbms_pipe, dbms_output etc on
oracle for work like this. But  in SQL I am facing too
many problems.

I am sure there must be a cool way to do it.

Help - please!!!

Sujata.

2. Newbie question

3. SQLExec stmt - field limit in select stmt with multiple tables

4. dBASE Announces Release of dB2K, The Information Toolset

5. Stored-Procedure: Construct Where stmt dynamically, how?

6. Help with Recordset

7. No. of rows selected by a SELECT stmt

8. I Need the Embedded SQL Toolkit or the "NSQLPREP.EXE"

9. SQL7 ODBC Generate stored proc for prepared stmt??

10. JDBC stmt.set.BinaryStream and stmt.setBytes (repost)

11. JDBC stmt.set.BinaryStream and stmt.setBytes

12. Dynamically generate a schema

13. dynamically generating column names