find first index of blank character in text in sql

Hi ,

I have string "This is test string" I want to extract the index of first blank space in this string in sql.

I just wrote this script for you:

declare @stringText as varchar(100)
declare @length as int
declare @counter as int
declare @currentChar as char

set @stringText = 'This istest string'
set @length = (select len(@stringText)) --get the total chars length of the string
set @counter = 0 --counter
while @counter <= @length
BEGIN
	set @currentChar = substring(@stringText,@counter,@length)
	if @currentChar = ' ' 
	begin
		if @counter = 0 set @counter = 1;
		break;
	end
	else set @counter = @counter + 1; -- increment the counter	
END

print(@counter-1) --if you want zero-based index, then @counter-1

DECLARE @myString VARCHAR(100)
DECLARE @index INT
SET @myString = 'This is test string'
SET @index = CHARINDEX(' ', @myString)
PRINT ( CONVERT(VARCHAR(10), @index) )

--OUTPUT
--5

raghav_khunger said:

 

DECLARE @myString VARCHAR(100)
DECLARE @index INT
SET @myString = 'This is test string'
SET @index = CHARINDEX(' ', @myString)
PRINT ( CONVERT(VARCHAR(10), @index) )

--OUTPUT
--5

Aww.. I really thought to start with Charindex but have chaned my mind :). Your solution is definitely shorter and faster! :)