topic Re: Delete Character from String In specific position in Discussions

Delete Character from String In specific position

LukeFarrell — Sat, 10 Jun 2023 23:39:32 GMT

Hello,

I am looking to create a script to remove a character from a string while ignoring the same character earlier in the string.

For example, from a data table I have a special DateTime of YY/MM/DD/hh:mm and wish to remove the third / (ie character 9).

Thank you for any help,

Luke

Re: Delete Character from String In specific position

Craige_Hales — Thu, 28 Oct 2021 02:24:30 GMT

There are many approaches. If the strings are always the same length, etc, I might use a formula column with

Left( DateTime, 8 ) || "T" || Right( DateTime, 5 );

You might want a separator like T which is used by one of the international formats, yyyy-mm-ddThh:mm.

If there is some variation, but maybe always the third /, I might use regex in the formula column

Regex( DateTime, "^(\d+/\d+/\d+)/(\d+:\d+)$", "\1T\2" );

^ and $ anchor the match to the beginning and end of the DateTime string. \d+ matches 1 or more digits. The parens make groups, and the two groups are referenced with \1T\2 to build the result value.

Re: Delete Character from String In specific position

LukeFarrell — Thu, 28 Oct 2021 13:03:05 GMT

Hello Craige, I have tried that but seem to be getting errors when trying to use it:

I may be understanding it incorrectly as I am attempting to create a new column with the data:

Also apologies, for some reason I was unable to send as a JLS script to required screencap

Re: Delete Character from String In specific position

jthi — Thu, 28 Oct 2021 15:37:00 GMT

Could be just some simple mistake with column references.

Names Default To Here(1);
dt = New Table("Untitled 5",
	Add Rows(3),
	New Column("DateTime", Character, "Nominal", Set Values({"21/10/24/15:21", "21/10/24/14:21", "20/09/20/15:20"}))
);

/*
other solutions
a = "21/10/24/15:21";
//other solution with Regex
Show(Regex(a, "^(.{8})(.)(.*)$", "\1 \3"));

//using Words and Concat Items
Show(Concat Items(Words(a, "/")[1 :: 3], "/") || " " || Word(4, a, "/"));

//Using Contains to get last "/" and then Substr()
lastIdx = Contains(a, "/", -1);
Substr(a, 1, lastIdx - 1) || " " || Substr(a, lastIdx + 1);
*/

dt << New Column("Compatible DateTime", Formula(Left(:DateTime, 8) || " " || Right(:DateTime, 5)));

(Note that the new column won't be Numeric Continuous without some extra jsl)