How to count consecutive letters in a field based on the alphabet

[theresakad]

I am struggling trying to figure out how to go about this. Your help is greatly appreciated.

I have about 6000 records. Field1 contains a series of characters in consecutive order of the alphabet. I need to know the number of consecutive letters starting from the end of the string. The alphabet starts over once Z is reached. I hope this is understandable. If more information is needed, please let me know.

I need to loop through each row and each character in field1 in that row and some how get a count for each record. Once i find that count i need to set another field in that record based on that count.

Sample Data

ID field1 Result Count
1 QRUVW 3
2 OPRSTUV 5
3 NP 1
4 EFGHIQRSUV 2
5 STUVWXYZA 9


If count = 3 then field2 will be bronze.
If count = 2 then fields will be silver.

 

[markros]

I most likely will solve it using Numbers (Tally) table and using ASCII of each character. This problem is not really trivial, but not too complex either.

PluralSight Learning Library

 

[simian336]

Put this into a function..


declare @test varchar(50)
set @test='STUVWXYZA'
declare @Currchar char(1)
declare @nextchar char(1)
declare @length int
set @length=len(@test)
declare @count int
set @count=0


while @count < @length
begin

set @currchar=substring(@test,@length-@count,1)
print @currchar

Set @count=@count + 1
Print @count

set @nextchar = (select
case @currchar
When 'Z' Then 'Y'
When 'Y' Then 'X'
When 'X' Then 'W'
When 'W' Then 'V'
When 'V' Then 'U'
When 'U' Then 'T'
When 'T' Then 'S'
When 'S' Then 'R'
When 'R' Then 'Q'
When 'Q' Then 'P'
When 'P' Then 'O'
When 'O' Then 'N'
When 'N' Then 'M'
When 'M' Then 'L'
When 'L' Then 'K'
When 'K' Then 'J'
When 'J' Then 'I'
When 'I' Then 'H'
When 'H' Then 'G'
When 'G' Then 'F'
When 'F' Then 'E'
When 'E' Then 'D'
When 'D' Then 'C'
When 'C' Then 'B'
When 'B' Then 'A'
When 'A' Then 'Z'
end)
print @nextchar

if @nextchar=substring(@test,@length-@count,1)
begin
--select @test as string, @length as length, @count as count,
--@currchar as currchar, @nextchar as nextchar
print @currchar
end
else
begin
select @count as count, @currchar as currchar
set @count = @length
print 'Out'
end
end



Simi

 

[Qik3Coder]

This is incomplete, but like simians, you'd put it in a function

Declare @_input varchar(200)
select @_input = 'aaaabcdefh'

Declare @_key varchar(27), @_keyPos int, @_keyChar varchar(1), @_inputPos int, @_inputChar varchar(1), @_inputLength int, @_ticker int, @_maxTick int, @_matchedSet varchar(200)
SELECT @_key = 'abcdefghijklmnopqrstuvwxyza', @_ticker = 1, @_maxTick=1, @_inputPos=1, @_inputLength = len(@_input), @_matchedSet = ''

select @_inputChar = substring(@_input, @_inputPos,1)
select @_keypos = charindex(@_inputChar, @_key)

while (@_inputPos <= @_inputLength)
 BEGIN
  --Get start position in key
  select @_keyChar = substring(@_key, @_keyPos, 1), @_inputChar = substring(@_input, @_inputPos, 1)
 -- print @_keyChar + ' = ' + @_inputChar
 -- print rtrim(@_keyPos) + ' | ' + rtrim(@_inputPos)
  IF(@_keyChar = @_inputChar)
   BEGIN
    SELECT @_matchedSet = @_matchedSet + @_keyChar, @_maxTick= CASE WHEN @_ticker > @_maxTick THEN @_ticker ELSE @_maxTick END, @_ticker = @_ticker + 1 
    SELECT @_inputPos = @_inputPos + 1, @_keyPos = @_keyPos + 1, @_keyChar= substring(@_key, @_keyPos, 1), @_keyPos = charindex(@_keyChar, @_key)
   END
  ELSE
    BEGIN
     SELECT @_ticker = 1
     SELECT @_matchedSet = @_matchedSet + '|'
     SELECT @_inputPos = @_inputPos + 1, @_keyPos = charindex(substring(@_input, @_inputPos,1), @_key)
     --print rtrim(@_keyPos) + ' | ' + rtrim(@_inputPos)
    END
  
 END 
 SELECT @_input, @_maxTick, @_matchedSet

You've got questions and source code. We want both!

 

[Qik3Coder]

There we go.
I had a problem with an odd number of stuttering letters, but got it resolved.

Declare @_input varchar(200)
select @_input = 'zabbabaaabcdefhabcde'

Declare @_key varchar(27), @_keyPos int, @_keyChar varchar(1), @_inputPos int, @_inputChar varchar(1), @_inputLength int, @_ticker int, @_maxTick int, @_matchedSet varchar(200)
SELECT @_key = 'abcdefghijklmnopqrstuvwxyza', @_ticker = 1, @_maxTick=1, @_inputPos=1, @_inputLength = len(@_input), @_matchedSet = ''

select @_inputChar = substring(@_input, @_inputPos,1)
--Get start position in key
select @_keypos = charindex(@_inputChar, @_key)
while (@_inputPos <= @_inputLength)
 BEGIN
  select @_keyChar = substring(@_key, @_keyPos, 1), @_inputChar = substring(@_input, @_inputPos, 1)
  IF(@_keyChar = @_inputChar)
   BEGIN
    SELECT @_matchedSet = @_matchedSet + @_keyChar, @_maxTick= CASE WHEN @_ticker > @_maxTick THEN @_ticker ELSE @_maxTick END, @_ticker = @_ticker + 1 
    SELECT @_inputPos = @_inputPos + 1, @_keyPos = @_keyPos + 1, @_keyChar= substring(@_key, @_keyPos, 1), @_keyPos = charindex(@_keyChar, @_key)
   END
  ELSE
    BEGIN
     SELECT @_inputPos = @_inputPos + CASE WHEN @_ticker > 1 THEN 0 ELSE 1 END, @_keyPos = charindex(substring(@_input, @_inputPos, 1), @_key), @_ticker = 1, @_matchedSet = @_matchedSet + '|'
    END
 END 
 
 SELECT @_input, @_maxTick, @_matchedSet

Lodlaiden

You've got questions and source code. We want both!

 

[simian336]

Create function CntConsec
(@test varchar(50))
returns int
begin

--declare @test varchar(50)
--set @test='EFGHIQRSUV'
declare @Currchar char(1)
declare @nextchar char(1)
declare @length int
set @length=len(@test)
declare @count int
set @count=0
Declare @myreturn int

while @count < @length
begin

set @currchar=substring(@test,@length-@count,1)
--print @currchar

Set @count=@count + 1
--print @count

set @nextchar = (select
case @currchar
When 'Z' Then 'Y'
When 'Y' Then 'X'
When 'X' Then 'W'
When 'W' Then 'V'
When 'V' Then 'U'
When 'U' Then 'T'
When 'T' Then 'S'
When 'S' Then 'R'
When 'R' Then 'Q'
When 'Q' Then 'P'
When 'P' Then 'O'
When 'O' Then 'N'
When 'N' Then 'M'
When 'M' Then 'L'
When 'L' Then 'K'
When 'K' Then 'J'
When 'J' Then 'I'
When 'I' Then 'H'
When 'H' Then 'G'
When 'G' Then 'F'
When 'F' Then 'E'
When 'E' Then 'D'
When 'D' Then 'C'
When 'C' Then 'B'
When 'B' Then 'A'
When 'A' Then 'Z'
end);
--print @nextchar

if @nextchar<>substring(@test,@length-@count,1)
begin
--select @count as count, @currchar as currchar
set @myreturn=@count
set @count = @length
--print 'Out'
end
end
return @myreturn
end


Put it in a function... appears to work fine.

Simi

 

[Qik3Coder]

Simian,
Interesting approach working backwards.
I had a problem were the test case had multiple repeating steps.

abc[!]abcde[/!]abc

Give the OP something to do...

Lod

You've got questions and source code. We want both!