Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Problem w/ Charindex and Reverse

    Hi, I am using SQL Server 2005. I have a few hundred records to bring in from a text file, simple enough right? There are only about ten columns and they are mixed datatypes, ie one int, a couple of dates, a member name(first and last together but oh well), a contract number that is another varchar, anyway the first 5 fields are fixed length but after that the name is in the middle and throws off the spacing for the rest of the data unfortunately. I am attempting to use the well known combination of charindex and reverse to work the rest of the data from the other end of the record if you will. Anyway for some odd reason when I charindex for the first field on the end by doing the charindex and reverse combination for some reason the first and only blank space that it recognizes is the one between the first and last names that is in the middle of the data. There are clearly blank spaces between all the fields but it won't recognize them and they are clearly before the name field. I have tried to force the collation, I just tried bringing it from a temp table to a permanent table and changing the datatype from varchar to char, and I also tried earlier to just take one entire record, only about 150 characters long and placing it in single quotes. I ran the charindex and reverse on it and it worked just fine. This is how I am doing it:

    select charindex(' ',reverse(MyFieldNameHere))

    Any ideas on this? I am really stumped. I have been doing this for about ten years and have never seen anything like it.

    ddave

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Sample data?

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dolfandave, The first thing I would check is whether there really are spaces where you think they are and not something else (like a TAB).

  4. #4
    Join Date
    Feb 2004
    Posts
    193
    I can try to grab some sample data later today when I am on site.

    As far as the tab vs space issue wouldn't that show when I copied and pasted it into a literal string? If not I will try it when I am w/ the client this evening. Thanks.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Check if it's unicode (open the file using debug from DOS, or use any binary editor), check for embedded nulls, and of course what Stealth suggested.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jun 2005
    Posts
    319
    hexeditor might help too

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    UltraEdit or GAWK makes this kind of problem simple to the point of being almost trivial. Think about giving either of them a try.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Feb 2004
    Posts
    193
    I am not familiar w/ either of those editors but will look into it. Just tweaked my query and sure enough it was a tab. Man, hours spent on that simple thing. It sure doesn't "look" like a tab, I mean it looks like a single space so I would expect a tab to be bigger. I realize it is still a single ascii character but that was my presumption and my mistake. Thanks again guys.

    ddave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •