September 17, 2002 at 6:20 am
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>
September 17, 2002 at 8:14 am
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.
September 17, 2002 at 8:15 am
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
September 18, 2002 at 5:08 am
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)
September 18, 2002 at 6:09 pm
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.
September 19, 2002 at 4:36 am
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>
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply