1. François Van Lerberghe
  2. Valentina Studio
  3. Пятница, Июль 14 2023, 04:00 PM
  4.  Подписаться через email
Using SQL, is there a way to find the position of a value in a string.
The field1 = '0351A', the field2 = '2392;0351A;3876B'
Field2 is build with the GROUP_CONCAT function

I would be able to find that field1 is in 2d position in field2.
How can I do that in Valentina
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hello,
It should be possible with a custom STORED FUNCTION based on LOCATE use in the loop.
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Here is an example of such a function:

CREATE OR REPLACE FUNCTION FindPos( inValue VARCHAR(255), inValues VARCHAR(255) )
RETURNS LONG
BEGIN
DECLARE pos LONG DEFAULT 1;
DECLARE R LONG;
DECLARE delimiterPos LONG;
DECLARE vals VARCHAR(255);

SET vals = inValues;
SET delimiterPos = LOCATE( ';', vals );

WHILE delimiterPos > 0 DO
IF LEFT( vals, delimiterPos - 1 ) = inValue THEN

SET r = pos;
RETURN r;
END IF

SET vals = SUBSTR( vals, delimiterPos + 1 );
SET delimiterPos = LOCATE( ';', vals );
SET pos = pos + 1;
END WHILE

IF vals = inValue THEN
RETURN pos;
ELSE
RETURN 0;

END IF
END;



And how it is used:

SELECT FindPos( '0351A', '2392;0351A;3876B' )
=>2
Комментарий
There are no comments made yet.
François Van Lerberghe Ответ принят
Thank you Sergey
Комментарий
There are no comments made yet.
  • Страница :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories