cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
lou
lou
Level III

Removing leading zeroes in a character column

Can Regex be used to strip leading zeroes from values in a character column?  If so, please provide an example.  If not, is there another method?

When leading zeroes occur, the number of leading zeroes can vary.  There are values in this column that are non-numeric.

 

Thanks,

Lou

1 ACCEPTED SOLUTION

Accepted Solutions
vince_faller
Super User (Alumni)

Re: Removing leading zeroes in a character column

This works for me

 

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 3 ),
	New Column( "test",
		Character,
		"Nominal",
		Set Values( {"000123", "0AB", "XYZ", "ABC0"} )
	)
);
dt << New Column("No Leading zeroes", character, <<Formula(Regex( :test, "^0*(.*)", "\1" )));
Vince Faller - Predictum

View solution in original post

3 REPLIES 3
txnelson
Super User

Re: Removing leading zeroes in a character column

I am not a regex coder, but here is a real simple example of how to do this using other JSL functions:

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 3 ),
	New Column( "test",
		Character,
		"Nominal",
		Set Values( {"000123", "0AB", "XYZ"} )
	)
);

For each row(
	while(substr(:test,1,1)=="0",
		:test = substr(:test,2)
	)
);
Jim
vince_faller
Super User (Alumni)

Re: Removing leading zeroes in a character column

This works for me

 

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 3 ),
	New Column( "test",
		Character,
		"Nominal",
		Set Values( {"000123", "0AB", "XYZ", "ABC0"} )
	)
);
dt << New Column("No Leading zeroes", character, <<Formula(Regex( :test, "^0*(.*)", "\1" )));
Vince Faller - Predictum
lou
lou
Level III

Re: Removing leading zeroes in a character column

Thanks!  That is what I needed.