topic Re: Replace function to insert "_" in data in App Development

Replace function to insert "_" in data

kailashkotak91 — Wed, 15 Nov 2017 00:22:55 GMT

Hello,

In my column (Unique identifier) I have data in this format 111122223333. I want to have data as 1111_2222_3333 format. This column has more that 150k records. How can i replace data from this format 111122223333 to 1111_2222_3333.

Can I do this in my load script when i load the unique identifier column? Thank you in advance!

Regards,

Kailash

Re: Replace function to insert "_" in data

kailashkotak91 — Wed, 15 Nov 2017 00:46:24 GMT

I need it in this format because i have to join this column with other table where the unique identifier is in 1111_2222_3333 format

Re: Replace function to insert "_" in data

luismadriz — Wed, 15 Nov 2017 01:37:04 GMT

Hi,

Maybe something like this:

Left(Field,4)&'_'&Mid(Field,5,4)&'_'&Right(Field,4) as FormattedField?

I hope this is helpful,

Cheers,

Re: Replace function to insert "_" in data

shraddha_g — Wed, 15 Nov 2017 08:19:01 GMT

provide some sample data to generalize expression

Re: Replace function to insert "_" in data

vinieme12 — Wed, 15 Nov 2017 08:26:08 GMT

then why don't you replace the '_' with '' in the key of your other tables? that is relatively simpler!

so all your keys are in 111122223333 format without the '_'

Re: Replace function to insert "_" in data

vinieme12 — Wed, 15 Nov 2017 08:28:03 GMT

also if you already have well formatted keys in other tables then you can also create a mapping table by replacing the '_' with null

Re: Replace function to insert "_" in data

kailashkotak91 — Wed, 15 Nov 2017 15:03:49 GMT

Hi Vineet,

Thanks for the suggestion. Can you give me an example. I can change the format of either of the table. Can you give me an example for it ?

Regards,

Kailash Kotak

Re: Replace function to insert "_" in data

rwunderlich — Wed, 15 Nov 2017 15:45:17 GMT

purgechar(key, '_')

-Rob

Re: Replace function to insert "_" in data

michael_solomon — Wed, 15 Nov 2017 22:21:17 GMT

I would be careful about removing the '_' as it is a character delimiter that can make all the difference as a unique identifier. If your field is always formatted the same way, Eg 12 characters long, then removing the '_' can work. However if the format is not structured, then removing the '_' may also remove the uniqueness. For example 1_23 and 12_3 would both be represented as 123 if you removed the '_', and therefore you can no longer use that field to distinguish between 1_23 and 12_3.

Re: Replace function to insert "_" in data

kailashkotak91 — Thu, 16 Nov 2017 19:58:23 GMT

Thank you everyone!