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:238448
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
