January 9, 2018 at 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 = '−'
January 9, 2018 at 12:45 pm
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
January 9, 2018 at 12:46 pm
texpic - Tuesday, January 9, 2018 12:12 PMI 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 − 546My 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
January 9, 2018 at 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.
January 9, 2018 at 1:00 pm
texpic - Tuesday, January 9, 2018 12:53 PMYes 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.
January 9, 2018 at 1:06 pm
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().
January 9, 2018 at 1:13 pm
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
January 9, 2018 at 1:29 pm
drew.allen - Tuesday, January 9, 2018 1:13 PMI 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?
January 9, 2018 at 1:48 pm
Luis Cazares - Tuesday, January 9, 2018 1:29 PMdrew.allen - Tuesday, January 9, 2018 1:13 PMI 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
January 9, 2018 at 2:30 pm
Luis Cazares - Tuesday, January 9, 2018 1:29 PMdrew.allen - Tuesday, January 9, 2018 1:13 PMI 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.
January 9, 2018 at 2:42 pm
Chris Harshman - Tuesday, January 9, 2018 2:30 PMLuis Cazares - Tuesday, January 9, 2018 1:29 PMdrew.allen - Tuesday, January 9, 2018 1:13 PMI 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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply