[SOLVED] Finding the position of a character from a string

Finding the position of a character from a string

I tried finding the position of a string with charindex function but when there are two letters in the same string, I'm not able to get the code.

My question is: Find the postion of S from 'SyedSohail' The output should be Position 1 5

Could you please help me with the above

I'm writing the code in Tsql

Solution

You can paste the following query right inside your SQL Editor:

DECLARE @test AS varchar(100);
DECLARE @ctr as int;
DECLARE @testlength as int;
DECLARE @charToTest as char(1);
DECLARE @positions as varchar(MAX);
DECLARE @findChar as char(1);

SET @test = 'Syed Summers';
SET @ctr = 1;
SET @testlength = LEN(@test) + 1;
SET @positions = '';
SET @findChar = 'S';

WHILE @ctr < (@testlength)
BEGIN
  SET @charToTest = SUBSTRING(@test, @ctr, 1)
  IF (UPPER(@charToTest) = @findChar)
    BEGIN
      SET @positions =  @positions + ',' + CONVERT(VARCHAR(10), @ctr)
    END
  SET @ctr = @ctr + 1
END

SELECT RIGHT(@positions, (LEN(@positions) - 1));

Explanation:

@test - will contain the string which you want to search

@ctr - counter to iterate through all the characters in your string @test

@testLength - length of your @test string

@findChar - the character which you want to count the instance in the string

@charToTest - the string being tested whether it equals your @findChar value

@positions - will be an enumerated string of the position where the @findChar was found in the @test string.

Note that I provided a simplistic answer to help guide the understanding for the person who posted the question.