looping through a string

• sam-433147

  Ten Centuries

  Points: 1221

  More actions

  May 18, 2007 at 4:00 am

  #91106

  how can i loop through a string '123456'

  and add +1 to each number so result will be '234567'

  thanks

• Ken McKelvey

  SSCoach

  Points: 18866

  More actions

  May 18, 2007 at 4:55 am

  #707372

  The following non-ANSI t-sql should do it:

  DECLARE @a char(6)

      ,@b varchar(6)

  SET @a = '123456'

  SET @b-2 = ''

  SELECT @b-2 = @b-2 + D.Number

  FROM (

          SELECT CAST (

                      CASE D1.Number

                      WHEN 9 THEN 0

                      ELSE D1.Number + 1

                      END

                      AS char(1)

                  )

          FROM (

                  SELECT CAST(SUBSTRING(@a, D2.Number, 1) AS tinyint)

                  FROM (

                          SELECT 1 UNION ALL

                          SELECT 2 UNION ALL

                          SELECT 3 UNION ALL

                          SELECT 4 UNION ALL

                          SELECT 5 UNION ALL

                          SELECT 6

                      ) D2 (Number) -- or use number table

              ) D1 (Number)

      ) D (Number)

  SELECT @b-2