Replacing Non-Numeric values

[dsadm_ws]

Hi,

I wanted to replace any Non-Numeric occurences in the data for Zip codes. I have succesfully done so for Zip_Code5. However, when I use the same logic to resolve fr Zip_Code4 it wont print any values.

Code: Select all

svChars = CHAR(0):CHAR(1):CHAR(2):CHAR(3):CHAR(4):CHAR(5):CHAR(6):CHAR(7):CHAR(8):CHAR(9):CHAR(10):CHAR(11):CHAR(12):CHAR(13):CHAR(14):CHAR(15):CHAR(16):CHAR(17):CHAR(18):CHAR(19):CHAR(20):CHAR(21):CHAR(22):CHAR(23):CHAR(24):CHAR(25):CHAR(26):CHAR(27):CHAR(28):CHAR(29):CHAR(30):CHAR(31):CHAR(45):CHAR(65):CHAR(66):CHAR(67):CHAR(68):CHAR(69):CHAR(70):CHAR(71):CHAR(72):CHAR(73):CHAR(74):CHAR(75):CHAR(76):CHAR(77):CHAR(78):CHAR(79):CHAR(80):CHAR(81):CHAR(82):CHAR(83):CHAR(84):CHAR(85):CHAR(86):CHAR(87):CHAR(88):CHAR(89):CHAR(90):CHAR(97):CHAR(98):CHAR(99):CHAR(100):CHAR(101):CHAR(102):CHAR(103):CHAR(104):CHAR(105):CHAR(106):CHAR(107):CHAR(108):CHAR(109):CHAR(110):CHAR(111):CHAR(112):CHAR(113):CHAR(114):CHAR(115):CHAR(116):CHAR(117):CHAR(118):CHAR(119):CHAR(120):CHAR(121):CHAR(122):CHAR(33):CHAR(34):CHAR(35):CHAR(36):CHAR(37):CHAR(38):CHAR(39):CHAR(40):CHAR(41):CHAR(42):CHAR(43):CHAR(44):CHAR(46):CHAR(47):CHAR(58):CHAR(59):CHAR(60):CHAR(61):CHAR(61):CHAR(62):CHAR(63):CHAR(64):CHAR(91):CHAR(92):CHAR(93):CHAR(94):CHAR(95):CHAR(96):CHAR(123):CHAR(124):CHAR(125):CHAR(126):CHAR(127)

svZip5 = If Trim(Convert(svCtlChars,"", ZIP5)) = '' or Len(Trim(Convert(svCtlChars,"", ZIP5))) < 5 Then ' ' Else Trim(Convert(svCtlChars,"", ZIP5))

The above works perfectly fine. When I use the same for the ZIP4 it is dropping the records.

So I tried the below without checking for the Non-Numeric characters.

Code: Select all

 
svZip4 = If Trim(NulltoValue(Zip4),' ') = '' or Len(Trim(NulltoValue(Zip4),' ')) <4  Then ' '  Else Trim(NulltoValue(Zip4),' ')) 

And it perfectly works fine. However, I need to replace any Non-Numeric values for this column as well.

I think an extra set of eyes might help, in understanding what am I missing here.

Will appreciate any help!

[ray.wurlod]

Trim(NulltoValue(Zip4)," ") can never equal "" unless Zip4 is exactly "".

[dsadm_ws]

Ray, yes we are checking for values equals to ''. Also, it works fine. Sometime Datastage acts wierd.

I thought CHAR(0) handles the null values? Itseems like it doesn't, so I had to handle the Null values first and then the actual logic.

Used the following:

Code: Select all

svZip4Cd = Trim(NullToValue(ZIP4,' '))

svZip5Null = Trim(NullToValue(ZIP,' '))

svZip4 = If Len(Trim(Convert(svCtlChars,"", svZip4Cd))) < 4 or Trim(Convert(svCtlChars,"", svZip4Cd)) = '' Then ' ' Else Trim(Convert(svCtlChars,"", svZip4Cd))

svZip5 = If Len(Trim(Convert(svCtlChars,"", svZip5Null))) < 5 or  Trim(Convert(svCtlChars,"", svZip5Null)) = '' Then ' ' Else Trim(Convert(svCtlChars,"", svZip5Null))

[ray.wurlod]

No, Char(0) is an actual character. Unfortunately, in ASCII it's called "NUL". It's the character used in C programming to terminate a string.

It's not the same as NULL, which represents "value unknown". You can only test IsNull() or IsNotNull(), or handle null with functions such NullToValue().