DB2 SQL Query Trim inside trim

问题:

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

回答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:

1. 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'.)
2. Then '-' gets added, so you have '12-'.
3. 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'.
4. Then this gets concatenated too and you finally get '12-345'.

回答2:

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

回答3:

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