join data on strings

Question

join data on strings

pri_sql

SSC-Addicted

Points: 468
More actions
March 11, 2011 at 9:57 am

#246727

I have following values stored in the table1 and table2
table1.column1 - "US aliance - data"
table2 .column1- "US aliance - data"
When I join both the tables based on the column1. It never return any data. However, Data is same in both the tables. I think space and hyphen is creating some problems in joining. Please suggest how to deal with this data. Thanks. you help is much appriciated.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The space and hyphen will not give trouble if they are the same in both. Are you absolutely sure that the data is exactly the same, no leading spaces case if it's a case-sensitive collation?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pri_sql SSC-Addicted Points: 468 More actions · Answer 2

Thanks for your response. Actually data looks same. Is there any way I could see if there are any special charactor?

Bala' Ten Centuries Points: 1186 More actions · Answer 3

It should return the data ..see the sample below

are the both columns have the same collation SQL_Latin1_General_CP1_CI_AS

if not add the collation in the where clause

CREATE TABLE USERACCOUNT

( USERNAME VARCHAR(100),

USERPASSWORD VARCHAR(100),

INSTITUTION int)

INSERT INTO USERACCOUNT VALUES ( 'US aliance - data','JOHN',1)

CREATE TABLE USERACCOUNT1

( USERNAME VARCHAR(100),

USERPASSWORD VARCHAR(100),

INSTITUTION int)

INSERT INTO USERACCOUNT1 VALUES ( 'US aliance - data','JOHN',1)

select * from USERACCOUNT a

join USERACCOUNT1 b

on a.USERNAME =b.USERNAME

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Is that column a char or a varchar?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 5

I use this at work to inspect text values that I can't figure out, usually my problem is at the end, hence the descending ordering.

Look at the decimal values found at http://www.asciitable.com/[/url] to figure out if you have control characters in there.

DECLARE @data varchar(255)

SET @data='paste your value here'

--inline Tally table from Lynn Pettis' article http://www.sqlservercentral.com/articles/T-SQL/67899/[/url]

;with BaseNum (

N

) as (

select 1 union all

select 1

),

L1 (

N

) as (

select

bn1.N

from

BaseNum bn1

crossjoin BaseNum bn2

),

L2 (

N

) as (

select

a1.N

from

L1 a1

crossjoin L1 a2

),

L3 (

N

) as (

select top ((abs(casewhen @pStartValue < @pEndValue

then @pEndValue

else @pStartValue

end -

case when @pStartValue < @pEndValue

then @pStartValue

else @pEndValue

end))/abs(@pIncrement)+ 1)

a1.N

from

L2 a1

crossjoin L2 a2

),

Tally (

N

) as (

select

row_number()over (orderby a1.N)

from

L3 a1

)

select N AS position,substring(@data,N,1) AS data,ASCII(substring(@data,N,1)) AS ASCIIvalue

from

Tally

WHERE DATALENGTH(@data)<N

ORDER BY position DESC

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 6

I had a problem with invisible characters awhile ago. Here's how I figured it out.

Invisible characters in text join[/url]

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

By now you should have a solution for your problem using the proposed hints.

I just wanted to mention I encountered a case study regarding join performance when using strings vs integers which I found very interesting.

http://sqlinthewild.co.za/index.php/2011/02/15/are-int-joins-faster-than-string-joins-2/

Simplified, but IMHO a nice and valid test case to think about.

Thanks again, Gail.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 8

Here's a unique way of doing the special / invisible character search. Your mileage may vary:

DECLARE @y xml;

SET @y=(SELECT MyID, MyColumn

from MyTable where MyColumn=''

FOR XML RAW, ELEMENTS XSINIL);

SELECT convert(varchar(5000),@y);

EDIT: You'll want to change the WHERE clause as appropriate

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

pri_sql SSC-Addicted Points: 468 More actions · Answer 9

pri_sql

SSC-Addicted

Points: 468

March 14, 2011 at 8:46 am

#1298367

Thanks everyone. It helps.