how to the index of certain number in sql server

Question

How to find an index of a certain number for a given value

For example, if my number is 1001

I need index of both the one's present in 1001 Ie. 1 and 4

I am using charindex.But with this I am able to get only charindex of first 1.

Why don't you store the characters in a relational format ... `DigitId, Digit, Index` then you can say `SELECT Index FROM Digits WHERE DigitId = 1001 AND Digit = 1`. This will be much nicer performance-wise than `SELECT Index FROM dbo.udfCrackDigitIndices(1001)`. Good luck indexing a table-valued UDF. — ta.speot.is, Jan 29 '14 at 12:19
@Santosh . . . Can you explain what problem you are trying to solve? There is probably a better solution. Also, is the number always four digits? — Gordon Linoff, Jan 29 '14 at 12:57
@GordonLinoff yes,it will be always 4 digits.its a binary conversion of a number(max 15).I am trying for a solution which is here [http://stackoverflow.com/questions/21428317/an-sql-function-for-radix-conversion-alogorithm/21431159#21431159] — Santosh, Jan 29 '14 at 13:34

M.Ali · Answer 1 · 2014-01-29T12:29:20.923

Declare @String VARCHAR(10) = '1001'

SELECT CHARINDEX('1', @String)  AS FirstOne
      ,CHARINDEX('1', @String, CHARINDEX('1', @String)+1) NextOne

Result

FirstOne  NextOne
   1         4

CHARINDEX has an optional 3rd Parameter [Start Index]

CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )

OR you can do something like this ....

Declare @String VARCHAR(10) = '110101'


DECLARE @Table TABLE ([Char] CHAR(1), [INDEX] INT)      
DECLARE @ChartoSearch CHAR(1) = '1'      
DECLARE @i INT = 0      

WHILE (LEN(@String) >= @i)
BEGIN
     INSERT INTO @Table ([Char], [INDEX])
     SELECT @ChartoSearch,CHARINDEX(@ChartoSearch, @String, @i)
     SET @i = @i + 1;
END

SELECT DISTINCT * FROM @Table

Result Set

╔══════╦═══════╗
║ Char ║ INDEX ║
╠══════╬═══════╣
║    1 ║     1 ║
║    1 ║     2 ║
║    1 ║     4 ║
║    1 ║     6 ║
╚══════╩═══════╝

score 0 · Answer 2 · answered Jan 29 '14 at 14:46

If the number is only four digits, then I would suggest an explicit case statement:

select (case when number = '0000' then 0
             when number = '0001' then 1
             when number = '0010' then 1
             when number = '0011' then 2
             when number = '0100' then 1
             when number = '0101' then 2
             when number = '0110' then 2
             when number = '0111' then 3
             when number = '1000' then 1
             when number = '1001' then 2
             when number = '1010' then 2
             when number = '1011' then 3
             when number = '1100' then 2
             when number = '1101' then 3
             when number = '1110' then 3
             when number = '1111' then 4
      end) as NumOnes

Or, do the calculation like this:

select ((case when number like '1%' then 1 else 0 end) +
        (case when number like '_1%' then 1 else 0 end) +
        (case when number like '__1%' then 1 else 0 end) +
        (case when number like '___1%' then 1 else 0 end)
       ) as NumOnes

You could also do these operations on the number as a number:

select ((case when number & 1 > 0 then 1 else 0 end) +
        (case when number & 2 > 0 then 1 else 0 end) +
        (case when number & 4 > 0 then 1 else 0 end) +
        (case when number & 8 > 0 then 1 else 0 end)
       ) as NumOnes

how to the index of certain number in sql server

2 Answers2