i figured it out through trial and error thanks
select case substring(firstname,0,charindex(' ',firstname,0))
when ' ' then firstname + upper(left(lastname,1)) + upper(right(lastname,1))
else substring(firstname,0,charindex(' ',firstname,0)) +
upper(left(lastname,1))
+ upper(right(lastname,1)) end username , firstname, lastname, email from
customers c, users u where c.customer_id = u.customer_id and u.customer_id =
#cookie.id#
> i purchase fusetalk basic edition and i wanted to do an external
> authentication into the forum when the user is already logged in adn
> the basic edition is more limited and i cannot customize it as i want.
> need to follow their rules.
>
> anyway what you showed me work but i need to add something else which
> i thought i kne ew how to modify your code but got an error.
>
> so basically i want to extract like you have
>
>
> SELECT CASE SUBSTRING(YourColumn, 0, CHARINDEX(' ', YourColumn, 0))
> WHEN '' THEN YourColumn
> ELSE SUBSTRING(YourColumn, 0, CHARINDEX(' ', YourColumn, 0))
> END YourColumn
> FROM YourTable
>
> i want to connect the extracted first name like you have with the
> first and last letter of the last name and call it username. so that
> if the first name is 'Richard A.' and the last name is 'Ruby' i would
> like the final output to be RichardRY
>
> i was working with the following
>
> select firstname + upper(left(lastname,1) + upper(right(lastname,1) as
> username
> from customers
> where customer_id = #cookie.id#
>
> how do i add that to your code and make it work?
>
> hey thanks!
>
>
> >Yup, it is. But before I show you an answer to that, can you not do
> it in CF
> >with ListFirst(yourQuery.FirstName, " ") when outputting?
> >
> >Here's the SQL for SQL Server:
> >
> > SELECT SUBSTRING(YourColumn, 0, CHARINDEX(' ', YourColumn, 0))
> > FROM YourTable
> >
> >This will return an empty string if the column doesn't have a space
> in it.
> >To get around that, try:
> >
> > SELECT
> > CASE SUBSTRING(YourColumn, 0, CHARINDEX(' ', YourColumn, 0))
> > WHEN '' THEN YourColumn
> > ELSE SUBSTRING(YourColumn, 0, CHARINDEX(' ',
> > YourColumn, 0))
> > END YourColumn
> > FROM YourTable
> >
> >Although I'm not happy with that and I'm sure it could be tidied up
> some.
> >
> >Adrian
> >
> >-----Original Message-----
> >From: [EMAIL PROTECTED] [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> >Sent: 21 April 2006 23:52
> >To: CF-Talk
> >Subject: extracting with SQL
> >
> >
> >what SQL code will do a list find?
> >
> >say the the FirstName column has 'Richard A.' and i only want to
> extract
> >'Richard' out of it with SQL query code.
> >
> >select ??(FirstName
> >from customers
> >where id = #cookie.id#
> >
> >is this doable with
SQL?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238452
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
