By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

grab data from 1 column into different column

P: 4
Dear all,

i need some help here. currently i have 1 table with multiple field.
1 of the field name PackSize contain data like ;;5000;ml;; or ;;;;60;Tablet etc.
So, i want to ask expert out there,how to grab the value inside PackSize into my other field such as field Vol,Volunit,VolDnm,VolDnmUnit,Package,PackageUnit.

here some example :

Let say ;;5000;ml;;
then the data should be like this :

Packsize | Vol | Volunit | VolDnm | VolDnmUnit | Package | PackageUnit

;;5000;ml;;| <null> | <null> | 5000 | ml | <null> | <null>

let say ;;;;60;Tablet

Packsize | Vol | Volunit | VolDnm | VolDnmUnit | Package | PackageUnit

;;;;60;Tablet | <null> | <null> | <null> | <null> | 60 | Tablet
Nov 27 '08 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 2,367
Could you please let us know how many different types of units you have in the column data? For Eg: ml, tablets etc..?
Nov 27 '08 #2

P: 4
for VolUnit there are 3 types (mg,kg,g)
for VolDnm there are 2 types (cubic inch,ml)
and for packagingUnit there are 3 types (tablets,capsule,units)

i'm a beginner for sql server. Hope all of you could help me on these.
Nov 27 '08 #3

P: 4
and actually in my table contain 40K rows of data
Nov 27 '08 #4

Expert 100+
P: 2,367
And in what format the data is stored in the column packsize?
I see for ml its 2 semi colons followed by a number and then a semicolon and a unit and for tablet I see 4 semicolons followed by a number and so on?...Can you post few more sample data for reference?
Nov 27 '08 #5

P: 4
PackSize field format is varchar (50)

here are my other sample data :

1 ) ;;5000;ml;;
2 ) 65;gm;;;;
3 ) ;;;;120;Capsule
4 ) ;;;;24;Tablet

each semicolons will separate the data into 6 different column. if there is no value between semicolon like this (;;20)....then the data stored into the 1st field and 2nd field will be null but in 3rd field will be 20
Nov 27 '08 #6

Expert 2.5K+
P: 2,878
Read this and modify the code the suit your requirement.

-- CK
Nov 27 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.