Cool! I went to bed after posting my reply, glad you got it sorted. I'm sure there's a nicer way to do it though, having the sub string code twice is a bit ugly. Can anyone think of a better way?
Adrian -----Original Message----- From: [EMAIL PROTECTED] [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 22 April 2006 02:53 To: CF-Talk Subject: Re: extracting with SQL 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:238461 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
