CODEWITHSUNDEEP
sql
user3591637
user3591637

Reputation: 499

How to separate string output?

I have a query which output a a column name member_name

select 
LEFT(member_name, charindex('/', member_name) - 1) + ' ' 
+ SUBSTRING(member_name, charindex('/', member_name) + 1, 1) as member_name

from member

It output the string with the last name and first letter of first name.

How can I output in the table with the first letter and last name in different columns?

For example to have it output something like this:

first | last name 
-----------------
J     | DOE

B     | DOE-DOE

Z     | SMITH

I made a http://sqlfiddle.com/#!6/c7a74/7

Upvotes: 0

Views: 87

Answers (3)

Tony
Tony

Reputation: 2754

select 
   substring(member_name, charindex('/', member_name) + 1, 1) as first_initial,
   left(member_name, charindex('/', member_name) - 1) as last_name
from
   member

Upvotes: 4

Tony
Tony

Reputation: 2754

Although I believe the best way to handle this situation is the SQL modification in my other answer, here's a way to do it in CF only, based on your fiddle's data layout.

<cfquery name="getMembers">
   select member_name from member
</cfquery>

<table>
   <tr><td>First</td><td>Last Name</td></tr>
   <cfoutput>
      <cfloop query="getMembers">
         <cfset aMember = ListToArray(member_name, "/")>
         <tr><td>#left(aMember[2],1)#</td><td>#aMember[1]#</td></tr>
      </cfloop>
   </cfoutput>
</table>

Upvotes: 0

Regular Jo
Regular Jo

Reputation: 5510

I don't have the reputation to reply to comments just yet, but as you asked in a comment in the answer, It can be done in cold fusion, but I'd think SQL is your better choice.

<cfoutput query="get_names">
  <cfset l_name = listfirst(member_name,"/")>
  <cfset f_initial = left(listlast(member_name,"/"),1)>
  <!--- if you want f_initial capitalized, use the below cfset rather than the above --->
  <cfset f_initial = ucase(left(listlast(member_name,"/"),1))>

  First initial: #f_initial#, Last name: #l_name#<br>
</cfoutput>

That would output, based on your fiddle:

First initial: J, Last Name: suarez
First initial: J, Last Name: suarez
First initial: S, Last Name: mejia
First initial: D, Last Name: orozco

It's a good exercise to help understand how cf works, and more importantly that there is an overlap between what your queries can do for you and what your code can but once again I recommend the sql solution.

Upvotes: 1

Related Questions