Pack Function

[jennilein]

I have a part number field in our data warehouse, which, unfortunately, is saved in various formats. For example, part number Y12345 can be stored in the warehouse as either Y 12345, or Y1234 5, or Y1234 5. This creates a problem because if I run a query to view the sales figures for each part, I get different rows for each format of the part...thus if not ran correctly, you will never see the true total sales.

I was told I could use the pack function to eliminate the spacing, regardless or where the spaces are.

I had it written like : pack ( part number ).

It's not working....any tips???

 

[Draoued]

I suppose you are using Impromptu.

Pack doesn't remove Space inside the field.

If you have an Oracle database, you can try with the function: or_Replace(String1 , string2, string3)
Where String1 is PartNumber Field
String2 = Space ' '
String3 = empty ''

So like or_replace(PartNumber, ' ' , '')

 

[jennilein]

Yes, I'm working in Impromptu.

Are there any other functions or tricks out there to remove all spacing???

 

[drlex]

jennilein,
Pack unfortunately removes only excess internal spaces, not all. Unless you can get a user defined function written (I can't offer), I suspect you'll need to do something clunky to remove the spaces using locate or substring.
If you think there's only going to be one break in the code, then using first-word may assist.
Something ugly like:

first-word(pack(part number)) + reverse(first-word(reverse(pack(part number))))

might work, although it will hit speed.
(you may need to put the first part in 'trim-trailing' and the second in 'trim-leading').

["] Veni, Vidi, Velcro. ["]

 

[drlex]

Apologies, I had overlooked REPLACE as it is a function unavailable to me.

["] Veni, Vidi, Velcro. ["]