Charindex returns 0 when searching for '-'

When using the following command:
SELECT TOP 1000 [Code1]
      ,CHARINDEX('-', [Code1])
      ,[Name]
      ,[Wdth]
      ,[Thck]
      ,[Lngth]
      ,[Dens]
      ,[Bwght]
      ,[Bpcs]
      ,[Btype]
  FROM [TestDB].[dbo].[product$]

I always get a 0 back from charindex even though all of the records have a hyphen in the code1 field.  Code1 holds text like 38-765432 in each record.  However, if I change the '-' to a number like '7' then the above code works fine.

Any suggestions on how I can get the position of the '-'.
LVL 1
strauchaAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
It sounds like the character between 38 and 765432 is not really a hyphen.
As you will see, this works just fine:
DECLARE @code1 VARCHAR(10) = '38-765432';
SELECT @code1, CHARINDEX('-', @code1);

Open in new window


Try selecting Code1, and copy-and-paste the value. You can then isolate that character for use in the CHARINDEX(). If that does not work, try posting the results of the Code1 values in a code snippet here and we can see what it shows up as.
0
 
VipulKadiaCommented:
Try this one :
SELECT TOP 1000 [Code1]
      ,CHARINDEX('-', CAST([Code1] as VARCHAR(100))
      ,[Name]
      ,[Wdth]
      ,[Thck]
      ,[Lngth]
      ,[Dens]
      ,[Bwght]
      ,[Bpcs]
      ,[Btype]
  FROM [TestDB].[dbo].[product$]

In this query, I have converted Code1 to VARCHAR(100). Please specify the size as you required instead of 100 and try.
0
 
strauchaAuthor Commented:
I'm not sure why, but it turns out that when I imported an Excel spreadsheet into a SQL table this text column turned the '-' (ASCII 45) into a longer dash (ASCII 150).

I just looked at the spreadsheet closer and the dash there appears to be longer than a normal hyphen.  This spreadsheet was given to me, but I assume that it was exported to Excel.  So the issue appears to be with the spreadsheet not the SQL import or commands.

Thanks for the input.
0
 
strauchaAuthor Commented:
It looks like a hyphen however it is a longer dash.  It is ASCII 150 not ASCII 45.  Appears to have happened when data was exported to Excel.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.