1

I want to get string from concatenate in while loop by step

1.get value(TmpAttrTH)

2.concatenate string(AttrTH) and value(TmpAttrTH)

My code like this

SET AttrTH = ''; -- First set AttrTH to blank
while i < CountAttr DO -- CountAttr is 2  and i set start at 0
    SET TmpAttrTH = (select thainame from attribute  where code = (select SPLIT_STR(atrMapping,'|',i) from attrcontrol where atrcode = AtrSet));
 -- TmpAttrTH  it getdata of loop example "stringloop1" , "stringloop2"
    SET i = i + 1;
 -- AttrTH in Concat function is old AttrTH string 
    SET AttrTH = CONCAT(AttrTH,TmpAttrTH);
 -- and SET AttrTH is new string will CONCAT(AttrTH,TmpAttrTH);   
END WHILE;
    select AttrTH  as str;

My result Expect "stringloop1stringloop2" but i don't know why i get null result.

I try to explain this problem too hard for me. pls help.

1
  • 1
    Can the task be done with a single SELECT ... GROUP_CONCAT(...) ...? Looping is a red flag with SQL.
    – Rick James
    Dec 14, 2017 at 5:46

1 Answer 1

0

CONCAT will return NULL if any of the inputs are NULL. This suggests that your select thainame... is returning NULL in at least one iteration.

CONCAT_WS will skip NULLs. Try using that instead and see what it gives you. Note the first argument is the desired separator, so if you want to duplicate CONCAT() but with NULL skipping just set that to an empty string, CONCAT_WS('',AttrTH,TmpAttrTH);

This should help you debug the problem. Alternately you can also use COALESCE on your SET TmpAttrTH if you want to set a default in cases where the result might be NULL. If you use COALESCE to set a default empty string you shouldn't get NULL back from the subsequent CONCAT.

Presuming the thainame is in Thai script, you may very well have collation/character set issues, which could be why it is returning null in the first place, your comparison may be failing. Check that all the columns you are comparing have the same character set/collation.

@Rick James is also entirely right that looping in SQL should be avoided, and likely is not necessary in the first place. So ideally rewrite the whole thing without the loop. But the above hopefully will help you get to the bottom of where the problem is.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.