strange issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

strange issue

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-24 : 15:03:30

this is very strange just wondering if anyone can see what i might be doing wrong
i have a table called transcations with vendortxcode being an nvarchar(50)

if i do
select dbo.trim(vendortxcode) from transcations where id=16479

I get the results and I can copy them so if it say 7f5g8c42b207df115df300137253cf79 I copy and do
select * from transcations where dbo.trim(vendortxcode)='7f5g8c42b207df115df300137253cf79'
but i get no results returned - it's on the same table - what could be the problem?

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 15:18:50

Collation mucking things up? (I'm doubtful about that, but we had a question recently where a simple EQUALS was failing for that reason)

Try this?


select * 
from transcations 
where dbo.trim(vendortxcode)='7f5g8c42b207df115df300137253cf79' COLLATE SQL_Latin1_General_CP1_CI_AS

P.S. Not sure its a good idea to call your UDF "trim" as that is a reserve word.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-24 : 15:36:42

nope that didn't help

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 15:52:14

Try this?


select *
from transcations
where dbo.trim(vendortxcode) = (select dbo.trim(vendortxcode) from transcations where id=16479)

if that works there is some strange character that is getting "lost" when you cut & paste the 7f5g8c42b207df115df300137253cf79 value that get output from the TRIM function.

If it doesn't work I have no good idea I'm afraid!

P.S. [transcations] looks like a spelling mistake, so the above may not run as-is

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-01-24 : 18:51:20

just curious but can you try this as well.


select * 
from transcations 
where convert(varchar(100),dbo.trim(vendortxcode)) ='7f5g8c42b207df115df300137253cf79'

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-25 : 01:28:22

vinnie that didn't work either
and yes the table name is spelled wrong

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 02:11:52

Did my suggestion work?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-25 : 02:18:21

kristen - i just noticed your post now and yes that works.
how can i figure out what the character is

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 02:46:37

I don't know

Only thing I can suggest is that you print out the ASCII value of each character


DECLARE @StrTemp @nvarchar(100), @intLoop int

select @StrTemp  = dbo.trim(vendortxcode) from transcations where id=16479

SET @intLoop = 1

WHILE @intLoop <= DATALENGTH(@nvarchar(100))
BEGIN
    SELECT @intLoop, SUBSTRING(@StrTemp, @intLoop, 1), ASCII(SUBSTRING(@StrTemp, @intLoop, 1))
    SELECT @intLoop = @intLoop + 1
END

Untested!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-25 : 02:50:35

The strange characters may be there if copy anything from EXCEL

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-25 : 03:07:57

i'm copying the new sql

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-25 : 03:08:13

sorry meant i'm copying from sql

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 03:57:57

Any joy with my test?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-25 : 05:56:25

WHILE @intLoop <= DATALENGTH(nvarchar(100))

what is this supposed to be - nvarchar or @ as you put it as it's returning an error

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 06:06:20

Sorry,

WHILE @intLoop <= DATALENGTH(@StrTemp)

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-25 : 06:19:30

so this returns name letter by letter with many spaces at the end

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 06:52:40

Should give you the Letter and the ASCII numeric value, so you can compare whether the letter you are seeing IS the Ascii value you expect.

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 06:53:39

"with many spaces at the end"

Ah ... you mean that there are rows of output with ASCII=32 / SPACE ??

Then your TRIM function is leaving some spaces on the end, so for an EQUALS test you will need to include those spaces in the comparison string.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-25 : 07:07:32

yes but i am using trim - shouldn't that get rid of all teh spaces?

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 07:29:45

Dunno, its a User Define Function, not a SQL one, isn't it? if so it will do whatever you, or your colleague, has programmed it to do

If you want the SQL function you need to use RTrim() instead of dbo.trim()

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-01-25 : 13:14:36

SSMS removes carriage returns when displaying rows. I assume that is the character that is throwing everything off. There is probably a return character at the end of the string. Try this


select * 
from transactions 
where replace(replace(Replace(vendorCode , CHAR(10), ''),char(13),'') ,' ','')='7f5g8c42b207df115df300137253cf79'

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources