Remove first character by Index

Asked

Viewed 49 times

1

Good afternoon.

I have a varchar with some numbers 

set @ID_CUSTOMERNUMBER = '01234567'

I would remove the zero only if it is the first number of my variable, example:

'0123456789' removing the zero would stay '123456789'

'123401234' can not remove the zero it is not the first.

I have the command 

PATINDEX('%0%', @ID_CUSTOMERNUMBER);

but it always returns the index, regardless of whether it is the first or not.

sql sql-server

  • One option is to convert to int and then back to scan. Up to how many digits the number can have?

1 answer

2


Just change the PATINDEX for that reason: %[^0]%'

And to remove the first character, you can use the SUBSTRING(), in this way:

DECLARE @ID_CUSTOMERNUMBER varchar(60)
SET @ID_CUSTOMERNUMBER = '01234567';

SELECT SUBSTRING(@ID_CUSTOMERNUMBER, patindex('%[^0]%',@ID_CUSTOMERNUMBER), 10)

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.