?
Solved

Charindex returns 0 when searching for '-'

Posted on 2011-10-18
4
Medium Priority
?
523 Views
Last Modified: 2012-05-12
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 '-'.
0
Comment
Question by:straucha
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36990590
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
 
LVL 8

Expert Comment

by:VipulKadia
ID: 36990659
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
 
LVL 1

Author Comment

by:straucha
ID: 36995472
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
 
LVL 1

Author Closing Comment

by:straucha
ID: 36995575
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question