Separate a column into two by a space or character

Forum for DBF Commander discussing.

Separate a column into two by a space or character

Postby PAM-Z » Tue Sep 06, 2016 10:18 pm

I have one column that I would like to separate into two columns, breaking at a space and the space may be at a different location with each entry. I have attempted to query using both instr and charindex functions to split the column in sql. Neither of these options seem to work. I can use substr; however, that is not much value if I can't determine how many characters I should use in the substr function. Does anyone have a suggestion, or advice?

I'd appreciate any suggestions you may have.

Thanks.
PAM-Z
 
Posts: 1
Joined: Tue Sep 06, 2016 6:50 pm

Re: Separate a column into two by a space or character

Postby Admin » Wed Sep 07, 2016 9:35 am

You can use combination of SUBSTR() and AT() functions, e.g.:
Code: Select all
SELECT SUBSTR(field1, 1, AT(' ', field1)) AS firstname, SUBSTR(field1, AT(' ', field1) + 1, 255) AS lastname FROM table1
Best regards,
Oleg Zhechkov
User avatar
Admin
Site Admin
 
Posts: 124
Joined: Wed Apr 21, 2010 7:27 pm


Return to DBF Commander Professional

Who is online

Users browsing this forum: No registered users and 6 guests

cron