467,149 Members | 1,200 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,149 developers. It's quick & easy.

grab data from 1 column into different column

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
  • viewed: 1560
6 Replies
Expert 2GB
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
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
and actually in my table contain 40K rows of data
Nov 27 '08 #4
Expert 2GB
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
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 2GB
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.

Similar topics

24 posts views Thread by Ehud Shabtai | last post: by
16 posts views Thread by D Witherspoon | last post: by
6 posts views Thread by Mike Kirkpatrick | last post: by
5 posts views Thread by Brian P. Hammer | last post: by
9 posts views Thread by Suresh | last post: by
reply views Thread by Christoph Haas | last post: by
19 posts views Thread by JRough | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.