Hej,
I have a column in my dataset that looks like this:
In order to match this column with another column in another table I need to exclude the '0' that appear in the beggining, not remove all 0 from all places, but just for those strings where it has the 1st place. As you can see '0' can appear at the end or in the middle of the string as well.
How can I achieve this?
| Column1 |
| 0114 |
| 0115 |
| 0117 |
| 0120 |
| 0123 |
| 0125 |
| 0126 |
| 0127 |
| 0128 |
| 0305 |
| 0319 |
| 0330 |
| 0331 |
| 0360 |
| 0380 |
| 0381 |
| 0382 |
| 0428 |
| 0461 |
| 0480 |
| 0481 |
| 0482 |
| 0483 |
| 0484 |
| 0486 |
| 0488 |
| 1060 |
| 1080 |
| 1081 |
| 1082 |
| 1083 |
| 1214 |
| 1230 |
| 2021 |
| 2023 |
| 2026 |
| 2029 |
| 2031 |
| 2034 |
| 2039 |
| 2061 |
| 2062 |
| 2080 |
| 2081 |
| 2082 |
| 2083 |
| 2084 |
| 2085 |
I've tryed to use the following on my Load statement which didn't gave the wanted result:
TestTable:
Load
Replace(Left(Column1,1),'0' ,'') as TestColumn
Resident Table1;
Desired result:
| Column1 |
|
Column1 (desired result) |
| 0114 |
|
114 |
| 0115 |
|
115 |
| 0117 |
|
117 |
| 0120 |
|
120 |
| 0123 |
|
123 |
| 0125 |
|
125 |
| 0126 |
|
126 |
| 0127 |
|
127 |
| 0128 |
|
128 |
| 0305 |
|
305 |
| 0319 |
|
319 |
| 0330 |
|
330 |
| 0331 |
|
331 |
| 0360 |
|
360 |
| 0380 |
|
380 |
| 0381 |
|
381 |
| 0382 |
|
382 |
| 0428 |
|
428 |
| 0461 |
|
461 |
| 0480 |
|
480 |
| 0481 |
|
481 |
| 0482 |
|
482 |
| 0483 |
|
483 |
| 0484 |
|
484 |
| 0486 |
|
486 |
| 0488 |
|
488 |
| 1060 |
|
1060 |
| 1080 |
|
1080 |
| 1081 |
|
1081 |
| 1082 |
|
1082 |
| 1083 |
|
1083 |
| 1214 |
|
1214 |
| 1230 |
|
1230 |
| 2021 |
|
2021 |
| 2023 |
|
2023 |
| 2026 |
|
2026 |
| 2029 |
|
2029 |
| 2031 |
|
2031 |
| 2034 |
|
2034 |
| 2039 |
|
2039 |
| 2061 |
|
2061 |
| 2062 |
|
2062 |
| 2080 |
|
2080 |
| 2081 |
|
2081 |
| 2082 |
|
2082 |
| 2083 |
|
2083 |
| 2084 |
|
2084 |
| 2085 |
|
2085 |
