none
'Blank' Character Issue

    Question

  • I'm trying to parse out a first and last name from a field where they could potentially combined. What I'm running into is that there is occaisonally a 'blank' entry in that field. Initially, I though it was just a character entry and I could filter them out. However, that is not the case. When getting the length of the field, it reports 0 as the length and no matter how I try and filter out the column, it is ignoring the filter and causes errors in my code. I've posted my code below to show what I'm doing. The error comes when the set of first case statements execute and when it tries to go back by 1 character so the comma is not included in the name.

     

    select

     

    case when last_name ='<Unknown>' then 'Unknown'

    when

     

    First_Name is null then

    SUBSTRING

     

    (last_name, 1, CHARINDEX(',',last_name, 1) -1)

    else

     

    last_name end as Last_name,

    case

     

    when last_name ='<Unknown>' then 'Unknown'

    when

     

    First_Name is null then

    SUBSTRING

     

    (last_name, CHARINDEX(',',last_name, 1) +1, len(last_name))

    else

     

    first_name end as First_name from Customer

    where

     

    (LEN(last_name) > 0 or last_name != ' ')


    Big Ern
    Monday, August 09, 2010 5:34 PM

Answers

  • Find the ascii character of your "blank value".  One of the columns where there is a blank copy it and paste it over <here>

    SELECT ASCII('<here>')

    this should give you a number, then in your where clause put the number is a char statement.... (This will be very slow, so I would not recomend for daily production use...this would be meant for a one time scrub off peak hours).  For instance lets say you got number 10 which is a line feed

     

    WHERE SUBSTRING(LTRIM(RTRIM(last_name)) , 1, 1) <> CHAR(10)

    • Marked as answer by BigErn782 Monday, August 09, 2010 7:32 PM
    Monday, August 09, 2010 6:39 PM

All replies

  • Maybe try something like this:

    SELECT
      CASE WHEN last_name = '<Unknown>' THEN 'Unknown'
         WHEN First_Name IS NULL AND CHARINDEX(',',last_name,1)>0
         THEN LEFT(CHARINDEX(',',last_name,1) - 1)
         ELSE last_name
      END AS Last_name,
      CASE WHEN last_name = '<Unknown>' THEN 'Unknown'
         WHEN First_Name IS NULL AND CHARINDEX(',',last_name,1) > 0
         THEN SUBSTRING(last_name,CHARINDEX(',',last_name,1) + 1,LEN(last_name))
         ELSE first_name
      END AS First_name
    FROM dbo.Customer
    WHERE last_name LIKE '[a-z]%'
    

    http://jahaines.blogspot.com/
    Monday, August 09, 2010 5:44 PM
    Moderator
  • Can you please re-post your code using Code block button from the toolbar (the last one). The simple trick to avoid an error is to add extra ,. e.g.

    substring(Last_Name, charindex(',', Last_Name + ',') + 1, LEN(last_Name))


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, August 09, 2010 5:51 PM
    Moderator
  • select
    case when last_name ='<Unknown>' then 'Unknown'
    whenFirst_Name is null then
    SUBSTRING(last_name, 1, CHARINDEX(',',last_name, 1) -1) 
    else last_name end as Last_name, 
    Case when last_name ='<Unknown>' then 'Unknown' 
    when First_Name is null Then 
    SUBSTRING (last_name, CHARINDEX(',',last_name, 1) +1, len(last_name)) 
    else first_name end as First_name from Customer 
    where(LEN(last_name) > 0 or last_name != ' ')
    

     

    Sorry about that.


    Big Ern
    Monday, August 09, 2010 6:13 PM
  • Still showing the blanks....
    Big Ern
    Monday, August 09, 2010 6:14 PM
  • Do you get an error with the code above or it doesn't return the desired result? Can you post few records as samples?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, August 09, 2010 6:19 PM
    Moderator
  • Find the ascii character of your "blank value".  One of the columns where there is a blank copy it and paste it over <here>

    SELECT ASCII('<here>')

    this should give you a number, then in your where clause put the number is a char statement.... (This will be very slow, so I would not recomend for daily production use...this would be meant for a one time scrub off peak hours).  For instance lets say you got number 10 which is a line feed

     

    WHERE SUBSTRING(LTRIM(RTRIM(last_name)) , 1, 1) <> CHAR(10)

    • Marked as answer by BigErn782 Monday, August 09, 2010 7:32 PM
    Monday, August 09, 2010 6:39 PM
  • Strangely, Kirk's response led me down the path of what the issue was. Being I forgot that I could check the ASCII value of the first character, I used that and I found that even though the last_name field was the field that had the 'blank' and ASCII Code 32 (space) value, somehow it was 'carrying' over into the first_name field and that was rasing the error. When I simply cleared the current where clause and put in where first_name != '', the error ceased and the batch processed. Here is a sample of the data I was filtering.

     

    First_Name   	Last_Name
    -------------   --------------
    John       Smith
    <null>      SMITH, JOHN
    <blank or ''>   SMITH, JOHN
    <Unknown>     SMITH, JOHN
    <Unknown>     <Unknown>
    <null>      <null>

    Big Ern
    Monday, August 09, 2010 7:37 PM