Weird Character

  • texpic

    SSCertifiable

    Points: 5882

    I imported data into SQL Server using a link server.  Imported an Excel version and a CSV version.  One cell has some fields with this weird character.  It isn't a dash or underscore.

    Underscore, dash, weird character
    _  -   −

    CSV                         Highland Park − 546
    Excel                        Highland Park − 546

    My goal is to use the import Excel version and be able to identify any field that has the weird character.  I tried CHAR and ASCII to try and identify/deal with this and can't seem to solve the puzzle.  Any ideas would be appreciated.  Thanks you.

    SELECT * FROM ExcelVersion WHERE MyField = '−'

  • drew.allen

    SSC Guru

    Points: 76735

    It looks like an en-dash.  On Windows keyboards, you can enter it by holding down the ALT key and entering 0150.  Some software can be configured to replace two adjacent hyphens with either an en-dash or an em-dash.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Sue_H

    SSC Guru

    Points: 90673

    texpic - Tuesday, January 9, 2018 12:12 PM

    I imported data into SQL Server using a link server.  Imported an Excel version and a CSV version.  One cell has some fields with this weird character.  It isn't a dash or underscore.

    Underscore, dash, weird character
    _  -   âˆ’

    CSV                         Highland Park − 546
    Excel                        Highland Park − 546

    My goal is to use the import Excel version and be able to identify any field that has the weird character.  I tried CHAR and ASCII to try and identify/deal with this and can't seem to solve the puzzle.  Any ideas would be appreciated.  Thanks you.

    SELECT * FROM ExcelVersion WHERE MyField = '−'

    Did you try copying the character and pasting it inside the quotes of something like: select ascii(' ') ? That would give you the ascii code.
    You could also pull up the character map on your PC and see if you can find the character.

    Sue

  • texpic

    SSCertifiable

    Points: 5882

    Yes i copy pasted it, just the "dash" and did get the dash character.  Apparently SQL must consider all the characters but I can't get the hidden characters on a copy/paste.  I've dealt with ASCII extended characters before but this one is not working out.

  • ZZartin

    SSC-Dedicated

    Points: 30414

    texpic - Tuesday, January 9, 2018 12:53 PM

    Yes i copy pasted it, just the "dash" and did get the dash character.  Apparently SQL must consider all the characters but I can't get the hidden characters on a copy/paste.  I've dealt with ASCII extended characters before but this one is not working out.

    Windows can be kind of wonky working with extended ascii characters, try converting the field to a varbinary, or just pull the original file up in a hex editor.

  • Luis Cazares

    SSC Guru

    Points: 183633

    That's probably because you need to handle the character as part of a unicode string. It's not the same to have '_--' than to have N'_--'. You should also use the UNICODE() function instead of ASCII().

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • drew.allen

    SSC Guru

    Points: 76735

    I copied and pasted the character from your post and I got that it was Unicode 8722 – the MINUS SIGN.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Luis Cazares

    SSC Guru

    Points: 183633

    drew.allen - Tuesday, January 9, 2018 1:13 PM

    I copied and pasted the character from your post and I got that it was Unicode 8722 – the MINUS SIGN.

    Drew

    Isn't it funny that there's only one key that would enter dashes, hyphens and minus sign but most of the times the user is not aware of what character will show up?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • drew.allen

    SSC Guru

    Points: 76735

    Luis Cazares - Tuesday, January 9, 2018 1:29 PM

    drew.allen - Tuesday, January 9, 2018 1:13 PM

    I copied and pasted the character from your post and I got that it was Unicode 8722 – the MINUS SIGN.

    Drew

    Isn't it funny that there's only one key that would enter dashes, hyphens and minus sign but most of the times the user is not aware of what character will show up?

    There isn't.  The key – at least on the US standard keyboard  – only ever produces a hyphen.  You need to enter special codes to get an en-dash, em-dash, or minus sign or configure software to replace certain combinations (usually two or three consecutive hyhpens) to get one of the other characters.  This is not really that surprising considering that most people aren't even aware that there are multiple similar characters, let alone when each should be used.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Chris Harshman

    SSC-Forever

    Points: 42104

    Luis Cazares - Tuesday, January 9, 2018 1:29 PM

    drew.allen - Tuesday, January 9, 2018 1:13 PM

    I copied and pasted the character from your post and I got that it was Unicode 8722 – the MINUS SIGN.

    Drew

    Isn't it funny that there's only one key that would enter dashes, hyphens and minus sign but most of the times the user is not aware of what character will show up?

    Sometimes it's software that changes it.  I know Microsoft Office programs such as Word and Outlook will change character 45 to 8211 depending on where in a line they are.

  • ZZartin

    SSC-Dedicated

    Points: 30414

    Chris Harshman - Tuesday, January 9, 2018 2:30 PM

    Luis Cazares - Tuesday, January 9, 2018 1:29 PM

    drew.allen - Tuesday, January 9, 2018 1:13 PM

    I copied and pasted the character from your post and I got that it was Unicode 8722 – the MINUS SIGN.

    Drew

    Isn't it funny that there's only one key that would enter dashes, hyphens and minus sign but most of the times the user is not aware of what character will show up?

    Sometimes it's software that changes it.  I know Microsoft Office programs such as Word and Outlook will change character 45 to 8211 depending on where in a line they are.

    I was messing with something I needed to change some characters to extended ascii in, fortunately just a config file, and it would display differently in notepad, wordpad, textpad, notepad++, MS word etc.....

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply