CHARINDEX always returns 0 when searching for spaces in the TEXT column

Question

--get text target starting integers into a table

declare @target TABLE([USERID] varchar(25),[target] int)

--get text stopping point integers into a table

declare @stop TABLE([USERID] varchar(25),[target] int, [stop] int)

--get just the options I want into a table

declare @options TABLE ([USERID] varchar(25), [userDetails] text)
insert into @options ([userid], [userDetails])
select u.userid, rtrim(ltrim(SUBSTRING([userDetails], s.[target], s.[stop] - s.[target])))
from users u join @stop s on u.userid = s.userid

declare @userDetails varchar(max)
Select top 1
@userDetails = [userDetails]
from @options
select charindex(char(32), @userDetails)

All I ever get is 0 from this charindex, I tried `` instead of char (32). I tried changing the text to varchar, but from what I see it is pointless, as the text becomes obsolete and is already considered as varchar?

When I copy and paste the userDetails snippet into the same line with quotation marks and put it in the charindex function, everything works as expected.

The source text that @userDetails fills looks like this:

key = value key = value key = value

Answer 1

" " SSMS, , . "Results to Grid", CRLF .

@PhilipKelley,

[tab]...

char (13) , Key = Value. , , CRLF, , ,

@Dems, ,

" " , ,

,

Answer 2

userDetailsIndex = patindex ('%%', userDetails)