spaces in id feild

Question

Post reply

spaces in id feild

sql_Reporter

SSCrazy

Points: 2329
More actions
December 12, 2005 at 8:42 am

#102980

Hey i want to join two part numbers but in some case there is a part number with a space in it this is caused by user input error,
Example part1_id = 123 456, part2_id = 123456
If i do a join on this it may or may not pick it up how do i get my query to join the 123456 togeter, as in look for a space join the number up together...

Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

ron k SSCertifiable Points: 6103 More actions · Answer 1

From BOL:

REPLACE

Replaces all occurrences of the second given string expression in the first string expression with a third expression.

Syntax

REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )

declare @MyString varchar(20)

set @MyString = '123 456'

select replace(@MyString, ' ','')

----------------------------------------------

123456

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

You can use replace() on the idcolumns to replace spaces with empty strings.

replace(id1, ' ','') = replace(id2,' ', '')

Note though that this will invalidate any indexusage on those columns enclosed by the functions.

What you really should try to do, is to clean the data once and for all, or even better, make sure that users can't enter id's with embedded spaces for starters. One also wonders how an ID number can be entered by a user..?

Though, if you're stuck between a rock and a hard place, replace() will let you work around the immediate problem, though it's not solved. You have corrupt id-values in there, and they should be taken care of.

Good luck

/Kenneth

sql_Reporter SSCrazy Points: 2329 More actions · Answer 3

Thanks,

Here is my code.

select ol.supplier_Part_Number,pd.pronum

from dw..products_dim pd

inner join ORDER_LINES ol on ol.supplier_Part_Number = pd.pronum

so how do i intergrate the code into it ?

sql_Reporter SSCrazy Points: 2329 More actions · Answer 4

sql_Reporter

SSCrazy

Points: 2329

December 12, 2005 at 9:46 am

#609051

Thanks its working fine now.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

It's customary to share your final solution, if you don't mind.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Korb SSCrazy Points: 2505 More actions · Answer 6

Users should not be entering parts ID numbers. Let them choose from a list of pre-existing, un-editable IDs.