Sql Question

Hi,

I have a text field with a semikolon separated list of values like:

myvalue=1;3;2;5;1;7;

Is there any sql command which can found a combination of parameters like "in list"

myvalue in list of "4;2;7;"

regards Michael

Michael
I will start by saying I am not an SQL guru but in similar circumstances I have found that I needed to test for each value separately in the WHERE clause...

WHERE myvalue LIKE '4%' AND myvalue LIKE '2%' AND myvalue LIKE '7%'

This should return records that contains the values 2,4 & 7 - The search will not be, however in any specific order so 2,4,7 - 7,2,4 etc would all be valid.
As far as I know you cannot search for a block in SQL if a delimiter is present - I look forward to being corrected.


A point worth mentioning is that this will only hold true if your 'myvalue' column only has values of 1 thru 9.
If for example you had values greater than '9' then '12;' would be included in the result as '2;' would be recognised by the search engine.
To avoid this you need to pad values 1 thru 9 with a zero when assigning the value to your 'myvalue' column.
Not an issue in my case but if you initial lookup has 100+ options then I guess your padding would need to be based on 2 x zero.

Depending on where you are in your developing cycle it may well be worth considering storing your 'myvalue' combinations as a JSON array.
I now do this as a matter of course and have found that as I can store more than the record ID - example + Description - my searches can be more specific.



Edited 1 time(s). Last edit at 04/11/2019 11:41AM by DerekT.

Michael,
I don't understand what you need.
In your DB you have one field with the content "1;3;2;5;1;7;" or you need to find records that have one of this values in one specific field?

Regards

Paulo Oliveira

Hi,

@pao: I need a SQL statement that works like "in list" but the database field is also a list.
I have a field which holds some Keyword_GUIDs, in my program the user can select one or more of them.

@derek: The problem is that the user can ask for 1 or many items, so the sql command with "and" or "or" can´t be static.


For now I have written a little procedure which runs after the sql and deletes all records which doesn´t match the keyword list. Not nice, but it works:

FOR ALL QRY_MasterToDo_Gesamt FROMEND
			FOR EACH STRING sStichwort_GUID OF stMyParaToDo.sStichwort_GUID_Liste SEPARATED BY ";"
				IF sStichwort_GUID<>"" THEN
					IF StringCount(QRY_MasterToDo_Gesamt.Stichwort_GUID,sStichwort_GUID)>0 THEN
						bLöschen=False
						BREAK
					END
				END
				bLöschen=True
			END
			IF bLöschen=True THEN
				HDelete(QRY_MasterToDo_Gesamt)
			END
		END

regards Michael

Hi Michael,

You could build an SQL text by programming and do a hExecuteSQLQuery.
In the sql text you build some lines based on the number of parameters
WHERE (x.x LIKE param1 OR x.x LIKE param2 OR etc )

Regards,
Piet



Edited 2 time(s). Last edit at 04/11/2019 01:39PM by pietvanz.

Hi Piet,

nope. Myfield.value is for example "2;1;5;7;"

that means:

WHERE "2;1;5;7;" IN (1,2,5,7) doesn´t work.

regards Michael

Michael
Yes, users can be a pain.

Best approach IMHO is to follow Piets suggestion and build an SQL text query.
With this approach you build your WHERE clause beforehand dependant on user selections then insert the appropriate AND or OR conditions by substitution using Stringbuild().

MyCond is string
if 'userselected = something' THEN MyCond = "AND" ELSE MyCond = "OR"

FOR EACH SelectedItem //From a list
s_Where += Stringbuild(" ListValue %1 ",MyCond)

Having done that you insert the WHERE statement into the main query text, again using Stringbuild() and use hExecuteSQLQuery()

sQryBuild is string = [
SELECT Val1, Val2, Val3
FROM Data
WHERE %1
]

sQryRun is string = Stringbuild(sQryBuild,s_Where )

OK, syntactically nonsense but gives a general idea.

@ all

thx for help.

Yes, a self build generic sql statement could be the solution.
My hope was that it exist a (for me unknown) superduper sql command which does this in seconds.
But it looks that we could next week drive to mars with Elon Musk but such a simple thing is a task for a genius :-)

regards Michael

I never used any of this functions but maybe you cand do it using FIND_IN_SET or REGEXP

[doc.windev.com]

Regards

Paulo Oliveira

Yes, find_in_set I also looked before, but


FIND_IN_SET('Red','Blue,Yellow,Red,Green')


Is not applicable

I need

FIND_SET_IN_SET('Red,Green','Blue,Yellow,Red,Green')

regards Michael