Subscribe to this thread
Home - General / All posts - SQL String trim and concat
lionel

995 post(s)
#10-Jun-22 12:54

A) StringConcat function information not avalable in documentation ?

using SQL wizard editor i have this StringConcat(<string>, <separator>, <string>)

using the documentation String SQL function , i don't find the definition of StringConcat location ( use here) !

Is there inside the documentation some information about StringConcat ( argument , return value ) ?

B) concat column text best way ?

What is the best way to concat column value with a seprarator ?

1) use StringConcat ?

with StringConcat could we do mix StringConcat([rg], ",", [b]) and rg=StringConcat([r], ",", [g])

so we have StringConcat(StringConcat([r], ",", [g]), ",", [b])

2) use expression StringConcat inside column name schema ?

3) use expression CAST() NVARCHAR in column name shema ?

CAST (''+[red]+','+[green]+','+[blue] AS NVARCHAR)

C) remove whitespace edge and inside a nvarchar

Strange i use : UPDATE [tablename] SET [col1]= StringTrim([col1],' ') and space(s) are not remove for example " 23" or " 0" for any value like "454" it is ok !! .

the white space perhaps is not a space so i really don't know howto remove this whitespace t !!

what is the best way to remove many space(s) that are continuous " " in begin and end of a string ?

here " "="space"="x" for see in html the number of space

Does transform gui can convert "x454xxx0xx76x" to "454x0x76" or "454 0 76"

D) Does TRansform Expression support FUNCTION END; ?

seem not !

Attachments:
space_not_remove.png


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

adamw


10,447 post(s)
#21-Jun-22 15:50

On StringTrim(..., ' ') with a space in the second parameter still not removing space, as shown on the screenshot -- I just checked and it seems to remove spaces just fine. I guess whitespace that you see consists of characters other than spaces. Try StringTrim(..., WHITESPACE) -- WHITESPACE is a built-in constant which includes space + tab + carriage return + line feed.

lionel

995 post(s)
#22-Jun-22 01:39

1) Using transform, i don't know wich ( replace i think) , i remenber to see only 3 options : Begin, end, Begin an end. It will be nice to have also "inside" and "all". This is for case of number 12437 is "12 437" and not "12437" .!

2) i ll test if transform gui support also this reserved word ! and also for all SQL function that contain trim and replace !!


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

lionel

995 post(s)
#26-Jun-22 15:48

about remove all spaceS

i confirm that SQL function call Stringtrim() does'nt work the way i expected I need .....

If i have MANY blank ( spaces tab ...) ) , it should remove ALL the space . So StringTrim is not the SQL Function i am looking foor !. it seem remove all the space before in the beginning before text occur and in the end of text but not inside !!

""" 12 475 5 """" return """12 475 5"""

IS there a function that do that ( i know regexp should work ..TODO)

about project file size

NB it is insane the size of mxb compare map file !!

Attachments:
trimTest file size.png
trimTest.mxb
trim_before-after.png


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

adamw


10,447 post(s)
#27-Jun-22 14:43

You can remove all spaces using StringRegexpReplace.

Example:

--SQL9

'[' + StringRegexpReplace(' abc def ghi   jkl   '' ''''i') + ']'

-- nvarchar: [abcdefghijkl]

The above snippet is a demo. Select the entire line that starts with '?' and invoke View - Run Selection. This should produce the line after that (I changed it into a comment for forum formatting). You can see that all spaces have been removed.

lionel

995 post(s)
#26-Jun-22 15:59

2 manifold chapters to read about witre SQL code :

Identifiers, Constants and Literals (manifold.net)

SQL Constants and Literals (manifold.net)


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.