T-SQL: compare strings

Question

T-SQL: compare strings

Steve Jones - SSC Editor

SSC Guru

Points: 736478
More actions
September 4, 2013 at 10:25 am

#297413

If you had this:
create table A
( id int
);
go
create table b
( mychar varchar(20)
);
go
insert a select 12345
insert a select 23456
insert b select '0012345'
insert b select '00012345'
Show how you can compare or join on these fields. Feel free to add a couple other fields for display.
Explain the issues with converting to integers, or pattern conversions to varchar

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Steve Jones - SSC Editor (9/4/2013)
If you had this:
create table A
( id int
);
go
create table b
( mychar varchar(20)
);
go
insert a select 12345
insert a select 23456
insert b select '0012345'
insert b select '00012345'
Show how you can compare or join on these fields. Feel free to add a couple other fields for display.
Explain the issues with converting to integers, or pattern conversions to varchar

Can I include the first step which would be "Hunt down the designer of this mess and feed him large quantities of high velocity pork chops?" 😀

--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)

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

No, but feel free to do a design article that covers a few things about why you wouldn't do this. Or how you might refactor this mess.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736478

January 31, 2014 at 11:10 am

#1685158

bump

Ed Pollack Hall of Fame Points: 3199 More actions · Answer 4

I'd be interested in taking this on. The quick example of joining on converted values would illustrate implicit conversions and the suboptimal execution plans that go along with it.

Some solutions can include: adding a new column to the table to handle a like data type value, creating an indexed temp table populated with the casted value, and some tips on designing better tables such that this doesn't happen in the first place---or it can be fixed later with a change to the table's design.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

Take it, Ed, but focus on one solution. I'd rather have focused articles than one that tries to solve all possibilities. If you'd like to do a series, then you can do a few that look at different solutions and we can group them together.

Ed Pollack Hall of Fame Points: 3199 More actions · Answer 6

Ed Pollack

Hall of Fame

Points: 3199

April 11, 2014 at 8:38 am

#1705337

Sounds good, thanks!