Page 1 of 1

[Solved] Find space in field in SQL query

PostPosted: Fri Feb 05, 2016 9:53 pm
by Nocton
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

Re: Find space in field in SQL query

PostPosted: Fri Feb 05, 2016 10:21 pm
by Sliderule
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.

Re: Find space in field in SQL query

PostPosted: Fri Feb 05, 2016 10:33 pm
by Nocton
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

Re: Find space in field in SQL query

PostPosted: Fri Feb 05, 2016 10:46 pm
by Sliderule
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.

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

PostPosted: Fri Feb 05, 2016 10:53 pm
by Nocton
Perfect, thank you.