DB2 SQL Query Trim inside trim

Question

I am transferring some SQL query into C# code, now I am having problem understand the following query. So far my understanding to the following query is: If PREFIX is between 0 to 99, then trim PREFIX, but what does || '-' || mean here? My understanding for line 3 is after finishing the trim function in line 2, do another trim, but I do not recognize the syntax in line 3 either . This is DB2.

            RETURN CASE WHEN PREFIX BETWEEN '00' AND '99'      //line 1               
            THEN TRIM(PREFIX) || '-' ||                        //line 2  
            TRIM(TRIM(L '0' FROM DIGITS(CLNUMBER)))            //line 3

are you sure this is SQL Server? SQL Server does not have a `TRIM` function nor does it use `||` to append characters — ughai, Apr 21 '15 at 14:08

score 1 · Answer 1 · answered Apr 21 '15 at 14:18

Your code does the following.

Line1: If you prefix is between '00' AND '99'

Line2: Then trim the spaces from prefix and then append -

Line3: Then append CLNUMBER by removing the leading 0 from CLNUMBER first

You can lookup the syntax of TRIM function here

score 1 · Accepted Answer · answered Apr 21 '15 at 14:31

1

DIGITS(CLNUMBER) returns the number stored in CLNUMBER as a string with leading zeros.
TRIM(L '0' FROM something) removes leading zeros from something.
TRIM(something) removes leading and trainling blanks from something.
|| concatenates strings.

PREFIX is a string. In case it contains a two-digit number, some processing gets done:

First you get that number trimmed, but because of the condition there can be no blank, so you get the original number string, e.g. '12'. (The condition would work on '01' but ignore ' 1' or '1'.)
Then '-' gets added, so you have '12-'.
Then you get CLNUMBER as a string with leading zeros and leading and trainling blanks removed. Let's say CLNUMBER contains '0345 ', then you'd get '345'.
Then this gets concatenated too and you finally get '12-345'.

answered Apr 21 '15 at 14:31

Thorsten Kettner

89,309
7
49
73

`(The condition would work on '01' but ignore ' 1' or '1'.)` There is no "condition" in the example that "ignores" those values. PREFIX is a string, so any characters (including blanks) might be involved. Of course, perhaps such a condition should be somewhere in there. The code implies that 'A1', '5R' and 'ZZ' are all possible. Of those, '5R' meets the CASE condition since it's between '00' and '99'. – user2338816 Apr 22 '15 at 06:58
@user2338816: You are right. '5R' does meet the condition and so will '1', I guess, whereas ' 1' is not in that range, because the blank is not between '0' and '9'. I was wrong, you are right; the CASE condition is not limited to two-digit numbers. – Thorsten Kettner Apr 22 '15 at 07:04

score 0 · Answer 3 · answered Mar 08 '18 at 02:35

0

or we can use as CAST( STRING_NAME as VARCHAR(no as per required) ) this works for removing trailing spaces for fixed no

answered Mar 08 '18 at 02:35

user9459849

1

DB2 SQL Query Trim inside trim

3 Answers3

Linked