Converting varchar to int

Question

Converting varchar to int

Viewing 6 posts - 31 through 35 (of 35 total)

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

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

I've been thinking about this and the performance. For the data at hand and if the table cannot be normalized, then Nigel's "like" method is probably the best.

Normalizing the table would still be the best, though, because then an Index could be made to come into play. Actually, there would be two normalized tables in this case....

--===== Simulate the original table

CREATE TABLE #OriginalProducts

(

Pid INT PRIMARY KEY CLUSTERED,

catid VARCHAR(100),

productname VARCHAR(50)

)

--===== Populate the original table simulation

INSERT INTO #OriginalProducts

(Pid,catid,productname)

SELECT 1,'1,2,3,7,8,11,15,16,17,18,19,20,21,','Ceramic Mug' UNION ALL

SELECT 2,'1,2,3,4,5,7,8,11,13,16,18,19,20,23,','t-shirts' UNION ALL

SELECT 3,'1,2,3,4,5,7,8,11,20,21,24,25,28,','calendars' UNION ALL

SELECT 4,'1,2,3,4,5,7,8,10,11,12,15,16,17,18,19,','greeting cards' UNION ALL

SELECT 5,'15,16,17,18,19,','pen'

--===== Show the content of the "original" table...

SELECT * FROM #OriginalProducts

--===== Create a new "normalized" table while splitting the data on the fly

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY op.Pid),

op.Pid,

SUBSTRING(','+op.catid,t.N+1,CHARINDEX(',',op.catid,t.N)-t.N) AS catid

INTO #NormalizedTableProdCat

FROM #OriginalProducts op

CROSS JOIN dbo.Tally t

WHERE N < LEN(op.catid)

AND SUBSTRING(','+op.catid,t.N,1) = ','

--===== Let's see what we have in the new table...

SELECT * FROM #NormalizedTableProdCat

--===== We also need a normalized Product table...

SELECT DISTINCT Pid, productname

INTO #NormalizedTableProducts

FROM #OriginalProducts

--===== ... and then we'll see what that looks like...

SELECT * FROM #NormalizedTableProducts

--===== Of course, you should add the necessary keys and indexes

-- to both of those tables.

--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)
Intro to Tally Tables and Functions

izhar-azati SSC Enthusiast Points: 189 More actions · Answer 2

Try:

SELECT * FROM #product

WHERE ','+catid+',' like '%,1,%'

nigel. SSChampion Points: 11636 More actions · Answer 3

izhar-azati (12/4/2009)
Try:
SELECT * FROM #product
WHERE ','+catid+',' like '%,1,%'

Yes, that would do it too. But proper normalization would be better, as Jeff recommends (if possible).

I guess my attempt at humour on my previous post was missed, it was a bit subtle. 😀

Nigel
How to post questions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

nigel. (12/4/2009)
izhar-azati (12/4/2009)
Try:
SELECT * FROM #product
WHERE ','+catid+',' like '%,1,%'
Yes, that would do it too. But proper normalization would be better, as Jeff recommends (if possible).
I guess my attempt at humour on my previous post was missed, it was a bit subtle. 😀

Heh... subtlety noted for the future, Nigel. 😛

Shifting gears, I've also done it the way izhar-azati suggested... makes for nice clean code. But I also wonder which is more effective... 3 LIKEs or 2 concatenations and a LIKE. As you say, the proper normalization would be better but I guess I'll have to make the time to do a comparison of all 4 (not forgetting the function) methods for performance and resource usage. We know the proper normalization will fare far better but this type of problem comes up a lot and frequently with the caveat that it's 3rd party software where the table cannot be normalized without breaking that software.

--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)
Intro to Tally Tables and Functions

james.hp123 Newbie Points: 5 More actions · Answer 5

hi

Char is a fixed-length data type, the storage size of the char value is equal to the maximum size for the column.

varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for the column.

use char when the data entries in a column are expected to be the same size. else use varchar

ultimate acai max
Enhanced Kre-Alkalyn

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

james.hp123 (12/5/2009)
hi
Char is a fixed-length data type, the storage size of the char value is equal to the maximum size for the column.
varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for the column.
use char when the data entries in a column are expected to be the same size. else use varchar

Hi.... good post but are you sure that you posted it to the right thread?

Also, VARCHAR has a bit more overhead than just the number of characters stored. If memory serves, it has an additional two bytes having to do with size and an additional "Nullability" bit or byte (don't remember which) if defined as NULL. If it goes "off page" because of the table definition, there is even more overhead involved.

--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)
Intro to Tally Tables and Functions