CHARINDEX always returns 0 on a search for white space in a 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've tried ' ' instead of char(32). I've tried changing the text to varchar but from what I can see that is pointless as text is getting deprecated and is already treated as a varchar?

When i copy and paste the userDetails piece out into a single quoted string and place it into the charindex function things work as expected.

The source text that fills up @userDetails looks like this:

key=value key=value key=value

There shouldn't be since i have that RTRIM(LTRIM( piece in there.. — William Dwyer, Feb 21 '12 at 20:16
I've even tried moving to position 2 with charindex(,,2) to avoid that and it still throws 0 back — William Dwyer, Feb 21 '12 at 20:17
It really appears that there isn't any space present according to CHARINDEX PATINDEX REPLACE etc... I can't figure out how I'm able to pull out varchars with HEX 20 white spaces peppered all over from the output of SSMS results grid via copy paste?? — William Dwyer, Feb 21 '12 at 20:33
Now that I mentioned that It appears they may be CRLF's or some other character. Results grid converts them to white spaces apparently? — William Dwyer, Feb 21 '12 at 20:34
When cutting and pasting from results grid, columns are (at least by default) delimited with TAB characters (ascii 9) — Philip Kelley, Feb 21 '12 at 20:39
Thanks Philip, I also found that in the results grid if there are CR LF's in the results of a single column as in my case they are converted to white spaces hex 20 characters... That was my problem! — William Dwyer, Feb 21 '12 at 20:52
http://stackoverflow.com/questions/3612817/ssms-2008-converts-control-characters-to-spaces-when-using-results-to-grid — William Dwyer, Feb 21 '12 at 21:01
@WilliamDwyer - As I think you've discovered from the question you linked: There is a world of difference between "white space" and CHAR(32). "White Space" covers tabs, carriage returns, and a whole host of other entities, but when displayed in the results panel, they are often *represented* as simple spaces. That is why when copying and pasting the values, CHARINDEX suddenly returns a non-zero value. But, as it stands, your queries demonstrate that your value does not contain CHAR(32) anywhere, and so must contain other control characters. — MatBailie, Feb 22 '12 at 10:48

score 3 · Accepted Answer · edited May 23 '17 at 12:17

Switch to "Results to Text" in SSMS, to see what is really output. If you are in "Results to Grid" CRLF's are converted to white spaces.

As @PhilipKelley mentioned in the comments,

the move between columns gets converted to [tab] characters...

Now searching for char(13) or Carriage Return allows me to find the character that is between each of my Key=Value pairs. It also explains why there are 2 white spaces, these are the CRLF that the program that was writing this data is using to break up each entry

@Dems, reminded me that

"White Space" covers tabs, carriage returns, and a whole host of other entities,

Also a similar topic discussed on this site in another discussion

score 0 · Answer 2 · answered Mar 22 '17 at 14:16

0

userDetailsIndex=patindex('% %', userDetails)

answered Mar 22 '17 at 14:16

MarkZ

1
1

1

Welcome to SO! Please remember to improve your answers by explaining what they are doing. Also, in this case, your answer only matches on the space character and not other whitespace characters. – Michael L. Mar 22 '17 at 14:41

CHARINDEX always returns 0 on a search for white space in a TEXT Column

2 Answers2