How to store volume of product in SQL Server
I am wondering how to store volume data in my database (volume of a bottle). Currently my table is set up like this.
CREATE TABLE [dbo].[LookupSize]
(
[idSize] [int] IDENTITY(1,1) NOT NULL,
[Size] [nchar](50) NULL,
CONSTRAINT [PK_LookupSize] PRIMARY KEY CLUSTERED
)
I have the stored looking like this
1 12oz-355ml
2 12.5oz-375ml
3 12.9oz-382ml
4 10oz-296ml
5 11oz-330ml
etc
How can I sort this data as it is, or is there a better way of structuring the table. I use this as a lookup table. Right now it sorts with the decimal coming before the whole number.
ID Size
3 10oz-296ml
15 11.5oz-340ml
13 11oz-330ml
18 12.5oz-375ml
1022 12.9oz-382ml
4 12oz-355ml
1020 13.5oz-400ml
This is the query I am using
SELECT
idSize, Size
FROM
LookupSize
ORDER BY
Size ASC
How do I sort it to achieve this result?
ID Size
3 10oz-296ml
13 11oz-330ml
15 11.5oz-340ml
18 12.5oz-375ml
4 12oz-355ml
1022 12.9oz-382ml
1020 13.5oz-400ml
Thank you for any assistance
sql-server edited Nov 24 '18 at 19:10
marc_s
580k13011181266
asked Nov 24 '18 at 19:05
Jeff HurleyJeff Hurley
105
add a comment |
I am wondering how to store volume data in my database (volume of a bottle). Currently my table is set up like this.
CREATE TABLE [dbo].[LookupSize]
(
[idSize] [int] IDENTITY(1,1) NOT NULL,
[Size] [nchar](50) NULL,
CONSTRAINT [PK_LookupSize] PRIMARY KEY CLUSTERED
)
I have the stored looking like this
1 12oz-355ml
2 12.5oz-375ml
3 12.9oz-382ml
4 10oz-296ml
5 11oz-330ml
etc
How can I sort this data as it is, or is there a better way of structuring the table. I use this as a lookup table. Right now it sorts with the decimal coming before the whole number.
ID Size
3 10oz-296ml
15 11.5oz-340ml
13 11oz-330ml
18 12.5oz-375ml
1022 12.9oz-382ml
4 12oz-355ml
1020 13.5oz-400ml
This is the query I am using
SELECT
idSize, Size
FROM
LookupSize
ORDER BY
Size ASC
How do I sort it to achieve this result?
ID Size
3 10oz-296ml
13 11oz-330ml
15 11.5oz-340ml
18 12.5oz-375ml
4 12oz-355ml
1022 12.9oz-382ml
1020 13.5oz-400ml
Thank you for any assistance
sql-server edited Nov 24 '18 at 19:10
marc_s
580k13011181266
asked Nov 24 '18 at 19:05
Jeff HurleyJeff Hurley
105
Can't you create a columnOnceSizeand a computed columnMLSize AS OnceSize * 29.574? Then select from your table and order byOnceSize ASC.
– Sami
Nov 24 '18 at 20:30
I agree with @Sami. Two new columns, sayOuncesandMilliliters, even if they're parsed from the text you already have, will give you much more flexibility in the long run than trying to do the parsing on a query by query basis.
– Eric Brandt
Nov 24 '18 at 23:03
add a comment |
I am wondering how to store volume data in my database (volume of a bottle). Currently my table is set up like this.
CREATE TABLE [dbo].[LookupSize]
(
[idSize] [int] IDENTITY(1,1) NOT NULL,
[Size] [nchar](50) NULL,
CONSTRAINT [PK_LookupSize] PRIMARY KEY CLUSTERED
)
I have the stored looking like this
1 12oz-355ml
2 12.5oz-375ml
3 12.9oz-382ml
4 10oz-296ml
5 11oz-330ml
etc
How can I sort this data as it is, or is there a better way of structuring the table. I use this as a lookup table. Right now it sorts with the decimal coming before the whole number.
ID Size
3 10oz-296ml
15 11.5oz-340ml
13 11oz-330ml
18 12.5oz-375ml
1022 12.9oz-382ml
4 12oz-355ml
1020 13.5oz-400ml
This is the query I am using
SELECT
idSize, Size
FROM
LookupSize
ORDER BY
Size ASC
How do I sort it to achieve this result?
ID Size
3 10oz-296ml
13 11oz-330ml
15 11.5oz-340ml
18 12.5oz-375ml
4 12oz-355ml
1022 12.9oz-382ml
1020 13.5oz-400ml
Thank you for any assistance
sql-server edited Nov 24 '18 at 19:10
marc_s
580k13011181266
asked Nov 24 '18 at 19:05
Jeff HurleyJeff Hurley
105
I am wondering how to store volume data in my database (volume of a bottle). Currently my table is set up like this.
CREATE TABLE [dbo].[LookupSize]
(
[idSize] [int] IDENTITY(1,1) NOT NULL,
[Size] [nchar](50) NULL,
CONSTRAINT [PK_LookupSize] PRIMARY KEY CLUSTERED
)
I have the stored looking like this
1 12oz-355ml
2 12.5oz-375ml
3 12.9oz-382ml
4 10oz-296ml
5 11oz-330ml
etc
How can I sort this data as it is, or is there a better way of structuring the table. I use this as a lookup table. Right now it sorts with the decimal coming before the whole number.
ID Size
3 10oz-296ml
15 11.5oz-340ml
13 11oz-330ml
18 12.5oz-375ml
1022 12.9oz-382ml
4 12oz-355ml
1020 13.5oz-400ml
This is the query I am using
SELECT
idSize, Size
FROM
LookupSize
ORDER BY
Size ASC
How do I sort it to achieve this result?
ID Size
3 10oz-296ml
13 11oz-330ml
15 11.5oz-340ml
18 12.5oz-375ml
4 12oz-355ml
1022 12.9oz-382ml
1020 13.5oz-400ml
Thank you for any assistance
sql-server sql-server edited Nov 24 '18 at 19:10
marc_s
580k13011181266
asked Nov 24 '18 at 19:05
Jeff HurleyJeff Hurley
105
edited Nov 24 '18 at 19:10
marc_s
580k13011181266
asked Nov 24 '18 at 19:05
Jeff HurleyJeff Hurley
105
edited Nov 24 '18 at 19:10
marc_s
580k13011181266
edited Nov 24 '18 at 19:10
marc_s
580k13011181266
edited Nov 24 '18 at 19:10
marc_s
580k13011181266
580k13011181266
asked Nov 24 '18 at 19:05
Jeff HurleyJeff Hurley
105
asked Nov 24 '18 at 19:05
Jeff HurleyJeff Hurley
105
asked Nov 24 '18 at 19:05
Jeff HurleyJeff Hurley
105
105
Can't you create a columnOnceSizeand a computed columnMLSize AS OnceSize * 29.574? Then select from your table and order byOnceSize ASC.
– Sami
Nov 24 '18 at 20:30
I agree with @Sami. Two new columns, sayOuncesandMilliliters, even if they're parsed from the text you already have, will give you much more flexibility in the long run than trying to do the parsing on a query by query basis.
– Eric Brandt
Nov 24 '18 at 23:03
add a comment |
Can't you create a columnOnceSizeand a computed columnMLSize AS OnceSize * 29.574? Then select from your table and order byOnceSize ASC.
– Sami
Nov 24 '18 at 20:30
I agree with @Sami. Two new columns, sayOuncesandMilliliters, even if they're parsed from the text you already have, will give you much more flexibility in the long run than trying to do the parsing on a query by query basis.
– Eric Brandt
Nov 24 '18 at 23:03
Can't you create a column
OnceSize and a computed column MLSize AS OnceSize * 29.574? Then select from your table and order by OnceSize ASC.– Sami
Nov 24 '18 at 20:30
Can't you create a column
OnceSize and a computed column MLSize AS OnceSize * 29.574? Then select from your table and order by OnceSize ASC.– Sami
Nov 24 '18 at 20:30
I agree with @Sami. Two new columns, say
Ounces and Milliliters, even if they're parsed from the text you already have, will give you much more flexibility in the long run than trying to do the parsing on a query by query basis.– Eric Brandt
Nov 24 '18 at 23:03
I agree with @Sami. Two new columns, say
Ounces and Milliliters, even if they're parsed from the text you already have, will give you much more flexibility in the long run than trying to do the parsing on a query by query basis.– Eric Brandt
Nov 24 '18 at 23:03
add a comment |
1 Answer
1
active
oldest
votes
There are two data values in the Size column. You should split that up into two columns. This will fix the sorting problem.
Now, if you want to stick to the existing database structure, you can use an expression and sort by that expression. The performance will be affected in case of large data sets with this approach.
SELECT
idSize, Size, SUBSTRING([Size], CHARINDEX('-', [Size]) + 1, 50) AS mlSize
FROM
LookupSize
ORDER BY
mlSize ASC
answered Nov 25 '18 at 3:27
Tina SebastianTina Sebastian
362
This did the trick. I wasn't sure how to achieve this. Thank you
– Jeff Hurley
Dec 7 '18 at 18:07
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53461482%2fhow-to-store-volume-of-product-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
There are two data values in the Size column. You should split that up into two columns. This will fix the sorting problem.
Now, if you want to stick to the existing database structure, you can use an expression and sort by that expression. The performance will be affected in case of large data sets with this approach.
SELECT
idSize, Size, SUBSTRING([Size], CHARINDEX('-', [Size]) + 1, 50) AS mlSize
FROM
LookupSize
ORDER BY
mlSize ASC
answered Nov 25 '18 at 3:27
Tina SebastianTina Sebastian
362
This did the trick. I wasn't sure how to achieve this. Thank you
– Jeff Hurley
Dec 7 '18 at 18:07
add a comment |
There are two data values in the Size column. You should split that up into two columns. This will fix the sorting problem.
Now, if you want to stick to the existing database structure, you can use an expression and sort by that expression. The performance will be affected in case of large data sets with this approach.
SELECT
idSize, Size, SUBSTRING([Size], CHARINDEX('-', [Size]) + 1, 50) AS mlSize
FROM
LookupSize
ORDER BY
mlSize ASC
answered Nov 25 '18 at 3:27
Tina SebastianTina Sebastian
362
This did the trick. I wasn't sure how to achieve this. Thank you
– Jeff Hurley
Dec 7 '18 at 18:07
add a comment |
There are two data values in the Size column. You should split that up into two columns. This will fix the sorting problem.
Now, if you want to stick to the existing database structure, you can use an expression and sort by that expression. The performance will be affected in case of large data sets with this approach.
SELECT
idSize, Size, SUBSTRING([Size], CHARINDEX('-', [Size]) + 1, 50) AS mlSize
FROM
LookupSize
ORDER BY
mlSize ASC
answered Nov 25 '18 at 3:27
Tina SebastianTina Sebastian
362
There are two data values in the Size column. You should split that up into two columns. This will fix the sorting problem.
Now, if you want to stick to the existing database structure, you can use an expression and sort by that expression. The performance will be affected in case of large data sets with this approach.
SELECT
idSize, Size, SUBSTRING([Size], CHARINDEX('-', [Size]) + 1, 50) AS mlSize
FROM
LookupSize
ORDER BY
mlSize ASC
answered Nov 25 '18 at 3:27
Tina SebastianTina Sebastian
362
answered Nov 25 '18 at 3:27
Tina SebastianTina Sebastian
362
answered Nov 25 '18 at 3:27
Tina SebastianTina Sebastian
362
answered Nov 25 '18 at 3:27
Tina SebastianTina Sebastian
362
362
This did the trick. I wasn't sure how to achieve this. Thank you
– Jeff Hurley
Dec 7 '18 at 18:07
add a comment |
This did the trick. I wasn't sure how to achieve this. Thank you
– Jeff Hurley
Dec 7 '18 at 18:07
This did the trick. I wasn't sure how to achieve this. Thank you
– Jeff Hurley
Dec 7 '18 at 18:07
This did the trick. I wasn't sure how to achieve this. Thank you
– Jeff Hurley
Dec 7 '18 at 18:07
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53461482%2fhow-to-store-volume-of-product-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can't you create a column
OnceSizeand a computed columnMLSize AS OnceSize * 29.574? Then select from your table and order byOnceSize ASC.– Sami
Nov 24 '18 at 20:30
I agree with @Sami. Two new columns, say
OuncesandMilliliters, even if they're parsed from the text you already have, will give you much more flexibility in the long run than trying to do the parsing on a query by query basis.– Eric Brandt
Nov 24 '18 at 23:03