asked on

Format SQL Query Results in C#

Hi,

I have a query that gets a person's LastName, FirstName, and MI. I would like to format the output in a ASP dropdown menu in this format:

LastName, FirstName MI

The query is "SELECT LastName, FirstName, MI FROM Person".

Is it possible to format and concatenate these fields together using C# or the SQL Query itself?

bhmahler

you can do it easily in SQL like so

"SELECT (FirstName + " " + MI + ". " + LastName) AS FullName FROM Person"

Bhavesh Shah

SELECT LastName, FirstName + ' ' + MI as NewName FROM Person

Bhavesh Shah

SELECT LastName + ',' + FirstName + ' ' + MI as NewName FROM Person

ASKER CERTIFIED SOLUTION

Lara F

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bhmahler

trofimoval is right, if any of your fields accept null, you should add ISNULL so that you are not returned a null result.

tvPrasad

You can concatenate both places (C# Or SQL Server)

if you wish to do at sql side then see above response,
if you wish to do in C# you can use below to concatenate

string formattedString= string.format("{0}, {1} {2}", LastName, FirstName, MI);

MattStewart01

ASKER

This works perfectly to get the result that I need:

SELECT isnull(FirstName+' ','')+ isnull( MI+'. ',' ') + isnull(LastName,'')) AS FullName FROM Person

Another question though, would it be possible to pull the name back apart to be inserted into separate fields in the table?

In other words, if I have the variable fullName = "John, Smith N.", could I split it into firstName, lastName, and MI?

Lara F

select parsename(replace(replace(replace('John, Smith N',',','.'),' ','.'),'..','.'),1)
, parsename(replace(replace(replace('John, Smith N',',','.'),' ','.'),'..','.'),2)
, parsename(replace(replace(replace('John, Smith N',',','.'),' ','.'),'..','.'),3)