[Solved] Find space in field in SQL query

[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

[Sliderule]

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

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.

[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

[Sliderule]

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

Code: Select all

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.

[Nocton]

Perfect, thank you.