When 3 then trim(t ' ' from trim(l '0' from numinchar)) When 2 then ' '||(trim(t ' ' from trim(l '0' from numinchar)))
When 1 then ' '||(trim(t ' ' from trim(l '0' from numinchar))) Trim(t ' ' from (trim(l '0' from numinchar))) as new,Ĭase length(trim(t ' ' from trim(l '0' from numinchar))) If the justification does matter, take the last statement one step further, going back into the CASE logic. Trim(t ' ' from (trim(l '0' from numinchar))) as new No: Display the number with no leading zeros.Ī simpler statement that does not right-justify the numbers is: Yes: Display that line as the number with an extra space at the front, and trim leading zeros. No: Is the third digit of the field (when it has no leading zeros) equal to a space? Yes: Display that line as the number with two extra spaces at the front and trim leading zeros. Is the second digit of the field (when it has no leading zeros) equal to a space? The SUBSTR is used on the result of the TRIM statement to look at 1 digit of the resulting number to plug into the CASE statement and decides how to display the final number for each iteration of the CASE statement. The TRIM function is used to take off the leading zeros. This removes the leading zeroes and right-justifies the numbers as if they are in a numeric field. This works however, the many layers of CASE logic gets hard to read. Using CASE statements, the following SQL statement will align the numbers:Ĭase substr(trim(l '0' from numinchar),2,1) when ' ' thenĮlse case substr(trim(l '0' from numinchar),3,1) when ' ' thenĮlse trim(l '0' from numinchar) end end as temp Following is an example of how the data looks if it was in a numeric field: Here are some statements that someone can use to try and get the numbers looking better while keeping them in a character field.įor the example, make a 6-digit field that is never filled with more than 3 (non-zero) digits. However, if the number is in a character field rather than a numeric field, there can be problems getting the numbers to look correct. Normally with a number, it does not matter if a user puts in leading zeros during data entry.
0 kommentar(er)
