Search code examples
sql-serverwhile-loopcharindex

Loop through a character in sql

DECLARE @valueList varchar(8000)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)

SET @valueList = '00000129563698'

set @pos = 0
set @len = 0

WHILE CHARINDEX('0', @valueList, @pos+1)<>0
BEGIN
    set @len = CHARINDEX('0', @valueList, @pos+1) - @pos
    set @value = SUBSTRING(@valueList, @pos, @len)

    PRINT @value

    set @pos = CHARINDEX('0', @valueList, @pos+@len) +1
END

I want to remove the zeros from @valueList variable and store the remaining part in another variable.

How can I do this in sql?

Solution

  • You can do this using the PATINDEX function to look for the first non-zero digit;

    DECLARE @valueList varchar(8000)

SET @valueList = '00000129563698'

SELECT SUBSTRING(@valueList, PATINDEX('%[1-9]%', @valueList), 8000)