6 replies to this topic

[reachpradeep]

can any one tell this query
having the table emp
emp_id emp_name
01 john paul
02 ravi kumar
03 hari babu

when retriving the record i want the query output of emp_name is

paul john
kumar ravi
babu hari

if any one knows this please reply me

[LiLMsNinja]

Ohh snap.. i had to do this one day. However, i was forced to do the task in Excel rather than through a SQL query. Would excel be ok? If so, i'll dig up my ol' code and send it...

[LiLMsNinja]

Actually, i found it real quick. So i decided to go ahead and post it.

I created this code because i needed to create usernames for a bunch of users to a website that i work on. The users names were given to me in the same cell with first name and last name. The naming convention for the usernames had to be first initial and last name.

In Excel 07 put the names in the first column(assuming that you copy the values from 'emp_name' straight into excel.). Then open the developer tab, (Google how to toggle the display of the developer tab if it doesn't already shot) and select 'view code'. In there you will copy and past the following.

Or i guess you could just use the 'Text to Columns' functionality. LOL.

Public Sub lastWord2()

Dim stGotIt As String

Dim i As Integer


For i = 3 To 123

Cells(i, 7).Value = Left(Cells(i, 1).Value, InStr(1, Cells(i, 1).Value, " ") - 1)

stGotIt = StrReverse(Cells(i, 1).Value)

stGotIt = Left(stGotIt, InStr(1, stGotIt, " ", vbTextCompare))

Cells(i, 8).Value = StrReverse(Trim(stGotIt))

Next i


End Sub

I understand that at the current moment it is throwing an error; complaining about something. However, it still runs.. and does the job so just hit ok to the error.. and go on about your day. LOL! Hope that helps!

[debtboy]

LilMsNinja, thanks for the excel tip Awesome!!

The same function in SQL is someting like this:

SELECT
(SUBSTRING(emp_name,(CHARINDEX(' ', emp_name) + 1)) || ' ' || SUBSTRING(emp_name, 1,(CHARINDEX(' ', emp_name) -1))) AS 'EMPLOYEE NAME'
FROM emp

I'm doing this from my head so typos may exist along w/ differences in db syntax between SQL, Oracle, etc.., but you get the idea.

SUBSTRING or SUBSTR
|| or +

break the string at the space, get the trailing name (last) and
add a space then
concatenate with the beginning part of the string

good luck ;)

[LiLMsNinja]

Nice.. i'm adding this to my code snippets. Have i told you recently that your my hero?

[nop]

this also assumes that fnames and lnames are delimited by a space which may be unrealistic depending on your data input scheme...but sometimes it's good enough! i did something very similar for work one time with email addresses. that system was small enough that this was ok.

[debtboy]

nop said:

this also assumes that fnames and lnames are delimited by a space which may be unrealistic depending on your data input scheme...but sometimes it's good enough! i did something very similar for work one time with email addresses. that system was small enough that this was ok.

Yes exactly,
it's always good when someone else looks at
and evaluates the code. :thumbup1:

It works based on the sample provided, and if the conditions change
or the data format varies then the poster usually replies that it doesn't
work and then we get to the bottom of why. All too often, (as in this case)
the poster just disappears with no feedback at all. :mad: