Data Type Question

Question

Data Type Question

cmcilvoy

Ten Centuries

Points: 1015
More actions
September 17, 2002 at 6:20 am

#158096

Table:
productID int
productname varchar(200),
productsize varchar(25)
i have multiple products with different sizes. I store the sizes as a string because sometimes they have character data in them. However when i sort the values into a table i get
10
11
5
6
7
instead of
5
6
7
8
9
10
11
. how can i strip out the characters if there are any and then do a cast or convert to int. and also do it efficiently? thanks

</cm>

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

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

John Boals Old Hand Points: 321 More actions · Answer 1

If at all possible I'd make productid an int (or numeric if appropriate), and add a varchar column to capture the string data.

If you combine int and string data in a single column, in addition to the sort problems you're seeing, you'll also likely find it difficult to keep the data "clean". e.g., you're expecting to see something like:

6 inches

10 feet

14 cubic cm.

But unless the front end app is very good at verifying the input, you're likely to see something like:

six inches

'10 feet (user accidentally hit a key b4 typing 10)

14 cubic cm

Bottom line is, if you need to sort the data as an int, make the data an int.

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

Here is some code that I think might work for you. It orders the data based on the first integer contained in the productsize char field, regardless of where it is in the field.

CREATE TABLE PRODUCT (

PRODUCTID INT,

PRODUCTNAME VARCHAR(200),

PRODUCTSIZE VARCHAR(25))

INSERT INTO PRODUCT VALUES(1,'ABC','A10A')

INSERT INTO PRODUCT VALUES(2,'DEF','11A')

INSERT INTO PRODUCT VALUES(3,'GHI','5 A')

INSERT INTO PRODUCT VALUES(4,'JKL','6 A')

INSERT INTO PRODUCT VALUES(5,'MNO','8B')

select substring(productsize,patindex('%[0-9]%',productsize), len(productsize)) from product

order by cast (substring(substring(productsize,patindex('%[0-9]%',productsize), len(productsize)),1,patindex('%[^0-9]%',substring(productsize,patindex('%[0-9]%',productsize), len(productsize)))-1) as int)

drop table product

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Antares686 SSC Guru Points: 125444 More actions · Answer 3

The problem is exactly what JBoals states. Int will sort 1-10-100 however varchar because it is a dictionary sort order will sort 1,10,100,2,20,200,3,30,300... so either change the column (which would probably be best) or cast the coulmn to and int when ordering. In you case you need nothing more if this is a number.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Mike2265 Right there with Babe Points: 753 More actions · Answer 4

I agree with JBoals.

The data should be stored as an integer. Following normalised database structure you can then create another table - Units - with all the different possible units and store the UnitID in the product table.

Cheers,

Mike.

cmcilvoy Ten Centuries Points: 1015 More actions · Answer 5

Thanks for all the replies. However the data is coming from an extract and parsing the data into 2 columns is not feasible. Is there a standard function for finding non-integer characters?

I would like to first check for non-integrer characters and if none exist then do something like this

CONVERT(float, REPLACE(SizeValue, ' 1/2', '.5')).

</cm>