Query to cut off data 1

[florida41]

I have names in my table field called name:

John James/ABCD2
Carl Richards/EVV3
Richard Smith/AVR1

How can I query it so I just get the names so my results will be:

John James
Carl Richards
Richard Smith



My attempt didnt work:

select *
from tableNames
order by Mid(name, Instr(1, name, Chr(47))

 

[FoxProProgrammer]

If the "/" always divides the name from the other text, do the following:

Public Function getName()

Dim namefld As String

namefld = "Carl Richards/EVV3"

slash = InStr(1, namefld, "/")
namepart = Left(namefld, slash - 1)

End Function

You could call this function in your query.

If so, you need to get the position in the string where "/" occurs. Use that position in a Left function and retrieve

dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

sorry, remove the namefld = "Carl Richards/EVV3". I put that in there to test the code. You need to pass the name to the function.

Public Function getName(namefld)

slash = InStr(1, namefld, "/")
getName = Left(namefld, slash - 1)

End Function

Call the function with:

namepart = getName(yournamefld)




dz
dzaccess@yahoo.com

 

[florida41]

Thanks, this is a SQL in Cold Fusion. Anyway I can do it with just SQL?

 

[lespaul]

Try this:

SELECT Left([name], InStr(1, [name], "/")) As NameOnly From tableNames
ORDER BY Left([name], InStr(1, [name], "/"))


Leslie

 

[florida41]

Thanks that works but it outputs:

John James/
Carl Richards/
Richard Smith/

Anyway to get rid of the "/" part?

 

[lespaul]

move back one space in the LEFT function:

SELECT Left([name], InStr(1, [name], "/") - 1) As NameOnly From tableNames
ORDER BY Left([name], InStr(1, [name], "/"))


here's a little explanation:

Left([name], InStr(1, [name], "/") - 1)

the red section returns the position number of the "/"


InStr(1, "John James/", "/") = 11

when the 11 is passed to the LEFT function is says
return all the values of [name] starting at 1 and going until position number 11. Except 11 is where the / is, so what we really want is the position of the / minus a space, so I added a -1 to the end (in blue)


Leslie

 

[florida41]

Thanks for the solution and all the details!
It works great.