CHARINDEX searching for a space returns 0 when there are no alphabets in the string

I am trying to use CHARINDEX to find the position of a space in an alphanumeric field, and it is skipping the numbers and start counting at the position of the first alphabet if there is any. If there are no alphabets, it is returning 0. (I am using Management Studio Version 18.12.1).

SELECT Comment, CHARINDEX(' ', CAST(Comment AS VARCHAR(50) Collate SQL_Latin1_General_CP1_CI_AS) AS SpaceIndex FROM MyTable

Sample of the output is as shown below:

| Comment                                                | SpaceIndex |
| ------------------------------------------------------ | -----------|
| :16 0                                                  | 0          |
| 26 12 2 18 Internet Transfer 05/06/13 0:38ET Ref.499   | 20         |
| Internet transfer 10/09/17 04:09ST/EDT Ref.372322      | 9          |
| 292.74 64 12:38:07 0                                   | 0          |
| 00 12 2 18 Internet transfer 05/05/14 10:3ET Ref 312   | 20         |

I tried to recreate the table in my personal computer and populated the "Comment" field with the same values and I didn’t have any issues with the output when I used CHARINDEX. It returned 4, 3, 9, 7 and 3 consecutively for each record.

Could it be the version of the Management Studio that is causing the issue at work?

You need to sign in to view this answers