Locate command help

[chanle51]

This, I hope is an easy one:

I am attempting to break out in a text field all characters
before and after a blank space (but only blank spaces between two alpha characters.) I have tried many variations on the 'locate' command, but am unable to obtain the desired result.

(for example, if the text field reads as 'JOHN SMITH' I am attempting to break out the text into 2 new query result columns col1 = 'JOHN' col2 = 'SMITH'.)

left(name, locate(last, ' ')-1),
right(name, locate(last, ' ')+6)

Thanks from a newcomer. I am new and self taught, so pls. be gentle.

 

[karluk]

Which DBMS are you using? I don't think that locate is a standard ANSI SQL function, so the way to do this will probably vary depending on your environment.

SQL Server has a charindex function, so for this database your query would be something like the following:

select left(name,charindex(' ',name)-1),
ltrim(substring(name,charindex(' ',name)+1,len(name)))
from yourtable