| Author |
Topic |
|
duhaas
Constraint Violating Yak Guru
310 Posts |
 Posted - 2009-01-13 : 20:58:35
|
I'm selecting from a table that has a column called Sender which is a nvarchar(50). When I select something for example like:select sender from logs where sender = 'Duane' I get no resultsWhen i do this:select sender from logs where sender like 'Duane%' I get results. When I imported the data into this table, I did a rtrim and ltrim on the colum so cant understand what it would appear there is something after Duane. Any secretes or suggestions on tracking down the issue? I also have a smalldatetime column and am trying to match on just 1-1-2008 instead of having to do a match on 2006-01-17 07:58:00. Any suggestions there? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-13 : 23:50:24
|
it maybe a hard space then (ASCII 160). try thisSELECT sender from logs where replace(sender,char(160),'')='Duane' |
 |
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-01-14 : 08:58:10
|
| Didnt seem to do the trick either??? |
|
|
|
ron2112
Starting Member
44 Posts |
Posted - 2009-01-14 : 09:13:04
|
| Just as a troubleshooting step, try this:SELECT replace(sender,'Duane','') AS Character, asc(replace(sender,'Duane','')) AS AsciiCode from logsThat should give some indication what character is causing the problem. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-14 : 09:23:52
|
quote:
Originally posted by ron2112
Just as a troubleshooting step, try this:SELECT replace(sender,'Duane','') AS Character, ascii(replace(sender,'Duane','')) AS AsciiCode from logsThat should give some indication what character is causing the problem.
|
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-01-14 : 09:25:22
|
quote:
Originally posted by ron2112
Just as a troubleshooting step, try this:SELECT replace(sender,'Duane','') AS Character, asc(replace(sender,'Duane','')) AS AsciiCode from logsThat should give some indication what character is causing the problem.
Still nothing, pretty much returns all the results in the table with two columns, nothing distinguishable. |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-01-14 : 09:27:52
|
quote:
Originally posted by duhaas
quote:
Originally posted by ron2112
Just as a troubleshooting step, try this:SELECT replace(sender,'Duane','') AS Character, asc(replace(sender,'Duane','')) AS AsciiCode from logsThat should give some indication what character is causing the problem.
Still nothing, pretty much returns all the results in the table with two columns, nothing distinguishable.
Ok I lied, when i do that, it returns all the results, and for the name that Im replacing in this case Duane, all the Duane's are blanks now. |
|
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2009-01-14 : 10:57:26
|
| Hello,Not sure if you are still having the issue but here is how I replace blank spaces in partnumbers from an inventory DB.UPDATE InventorySET PartNumber=REPLACE(partnumber,' ','')Sounds to me from your last post that you ended up replacing all occurrences of DUANE with a blank space? If so you will want to fix that first..If you are getting no success with the LTRIM and RTRIM functions then it could be some other character that is being imported and not a space.Re-import your DUANE records and then do a select distinct where sender = 'DUANE%' and see how many records it returns. Take those records and export them to excel to see what character is really there.r&r |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-14 : 11:01:36
|
There might be carriage return or line feed characters and all other sorts of unprintable characters.Seems like a case of bad data cleansing.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-01-14 : 11:03:04
|
| Thanks for the advice, it was a CRLF that was causing my issue. Does anyone know how to get mgmt studio to show that CRLF in the results? I noticed it when i opened up the table itself in sql mgmt studio |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-14 : 11:05:02
|
If you choose TEXT mode, and not GRID mode, you can see the effect of the carriage returns and line feeds.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
Spaces maybe????