[Solved] Find space in field in SQL query

Creating tables and queries

[Solved] Find space in field in SQL query

Postby Nocton » Fri Feb 05, 2016 9:53 pm

I have field names in a table which have spaces in them, e.g.:
County Club
Town Club
Village Club

I want to run a query which will select the part of the field up to the space, e.g. to give:
County
Town
Village

I have tried using LOCATE (to be followed by LEFT,located position), but get errors, Any ideas, please?

Regards,

Nocton
Last edited by Nocton on Fri Feb 05, 2016 10:52 pm, edited 1 time in total.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 508
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Find space in field in SQL query

Postby Sliderule » Fri Feb 05, 2016 10:21 pm

Nocton wrote:I have field names in a table which have spaces in them, e.g.:
County Club
Town Club
Village Club

I want to run a query which will select the part of the field up to the space, e.g. to give:
County
Town
Village

I have tried using LOCATE (to be followed by LEFT,located position), but get errors, Any ideas, please?

Regards,

Nocton

Answer:

Code: Select all   Expand viewCollapse view
Select
   "County Club" as "County",
   "Town Club" as "Town",
   "Village Club" as "Village"
From "My_Table"

Explanation: Since your question said: "I have field names in a table which have spaces in them" ( and you are NOT talking about the data content, but rather, the column name that you assigned, per your description ) use an ALIAS as above . . . for example . . . as "County" .

I hope this, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: Find space in field in SQL query

Postby Nocton » Fri Feb 05, 2016 10:33 pm

Sorry, I was talking about the data content. I should have said:
I have data in a table which have spaces in them, e.g.:
County Club
Town Club
Village Club
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 508
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Find space in field in SQL query

Postby Sliderule » Fri Feb 05, 2016 10:46 pm

Assuming the name of your table is: "MY_TABLE" and the name of the column is: "MY_COLUMN"

Code: Select all   Expand viewCollapse view
Select
   "MY_TABLE".*,
   LEFT("MY_TABLE"."MY_COLUMN", LOCATE( SPACE(1), "MY_TABLE"."MY_COLUMN") - 1) as "MY_NEW_COLUMN"
From "MY_TABLE"

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Find space in field in SQL query

Postby Nocton » Fri Feb 05, 2016 10:53 pm

Perfect, thank you.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 508
Joined: Fri Nov 05, 2010 10:27 am
Location: UK


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest