How to get a part of field in select qurey?
This is a discussion on How to get a part of field in select qurey? within the MySQL forums, part of the Database Server Software category; --> Hello. In the table I have one column where I storage some data in format: Ac01,bc32,45sw Field contains a ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-29-2008, 10:40 AM
| ||||
| ||||
 How to get a part of field in select qurey? Hello. In the table I have one column where I storage some data in format: Ac01,bc32,45sw Field contains a few part of data coma separated. +----------------+ | types | +----------------+ | Ac01,bc32,45sw | | df22,Ac45 | | dd22 | | we23,rd34,Ac33 | +----------------+ I need to get a part of the field, for example data pass to pattern Ac % Demand result is: Aco1 Ac45 Ac33 Do somebody knows how to do it using mysql function?      |
|
05-29-2008, 10:40 AM
| |||
| |||
| Re: How to get a part of field in select qurey? Andrzej Kubaczyk wrote: > Hello. > > In the table I have one column where I storage some data in format: > Ac01,bc32,45sw > Field contains a few part of data coma separated. > > +----------------+ > | types | > +----------------+ > | Ac01,bc32,45sw | > | df22,Ac45 | > | dd22 | > | we23,rd34,Ac33 | > +----------------+ > > I need to get a part of the field, for example data pass to pattern Ac > % > > Demand result is: > Aco1 > Ac45 > Ac33 > > Do somebody knows how to do it using mysql function? Rather, you should normalize your database. Right now it doesn't meet the requirements for 1NF. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ================== |
|
05-29-2008, 10:40 AM
| |||
| |||
| Re: How to get a part of field in select qurey? Andrzej Kubaczyk wrote: >> Rather, you should normalize your database. Right now it doesn't meet >> the requirements for 1NF. > > You're right, I know that database is not normalized but I can't do it > for some reasons. Then you have problems. You will have to continue to try all kinds of workarounds like this as long as your database structure is bad. I understand you can't change that. And I feel sorry for you - and your customer. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ================== |
|
05-29-2008, 10:40 AM
| |||
| |||
| Re: How to get a part of field in select qurey? Andrzej Kubaczyk schreef: > Hello. > > In the table I have one column where I storage some data in format: > Ac01,bc32,45sw > Field contains a few part of data coma separated. > > +----------------+ > | types | > +----------------+ > | Ac01,bc32,45sw | > | df22,Ac45 | > | dd22 | > | we23,rd34,Ac33 | > +----------------+ > > I need to get a part of the field, for example data pass to pattern Ac > % > > Demand result is: > Aco1 > Ac45 > Ac33 > > Do somebody knows how to do it using mysql function? mysql> select nr, types, mid(types,locate('Ac',types),4) from actypes where types like '%Ac%'; +----+----------------+---------------------------------+ | nr | types | mid(types,locate('Ac',types),4) | +----+----------------+---------------------------------+ | 1 | AC01,bc32,45sw | AC01 | | 4 | df22,Ac35 | Ac35 | | 5 | we23,rd34,Ac33 | Ac33 | +----+----------------+---------------------------------+ 3 rows in set (0.00 sec) -- Luuk |
|
05-29-2008, 10:40 AM
| |||
| |||
| Re: How to get a part of field in select qurey? On 24 Maj, 15:36, Luuk <L...@invalid.lan> wrote: > Andrzej Kubaczyk schreef: > > > > > Hello. > > > In the table I have one column where I storage some data in format: > > Ac01,bc32,45sw > > Field contains a few part of data coma separated. > > > +----------------+ > > | types | > > +----------------+ > > | Ac01,bc32,45sw | > > | df22,Ac45 | > > | dd22 | > > | we23,rd34,Ac33 | > > +----------------+ > > > I need to get a part of the field, for example data pass to pattern Ac > > % > > > Demand result is: > > Aco1 > > Ac45 > > Ac33 > > > Do somebody knows how to do it using mysql function? > > mysql> select nr, types, mid(types,locate('Ac',types),4) from actypes > where types like '%Ac%'; > +----+----------------+---------------------------------+ > | nr | types | mid(types,locate('Ac',types),4) | > +----+----------------+---------------------------------+ > | 1 | AC01,bc32,45sw | AC01 | > | 4 | df22,Ac35 | Ac35 | > | 5 | we23,rd34,Ac33 | Ac33 | > +----+----------------+---------------------------------+ > 3 rows in set (0.00 sec) > > -- > Luuk Thank you Luuk, but I'm afraid my problem is more hard because lenght of substring Ac% is not constant (not eqaul 4)  |
|
05-29-2008, 10:40 AM
| |||
| |||
| Re: How to get a part of field in select qurey? Andrzej Kubaczyk schreef: > On 24 Maj, 15:36, Luuk <L...@invalid.lan> wrote: >> Andrzej Kubaczyk schreef: >> >> >> >>> Hello. >>> In the table I have one column where I storage some data in format: >>> Ac01,bc32,45sw >>> Field contains a few part of data coma separated. >>> +----------------+ >>> | types | >>> +----------------+ >>> | Ac01,bc32,45sw | >>> | df22,Ac45 | >>> | dd22 | >>> | we23,rd34,Ac33 | >>> +----------------+ >>> I need to get a part of the field, for example data pass to pattern Ac >>> % >>> Demand result is: >>> Aco1 >>> Ac45 >>> Ac33 >>> Do somebody knows how to do it using mysql function? >> mysql> select nr, types, mid(types,locate('Ac',types),4) from actypes >> where types like '%Ac%'; >> +----+----------------+---------------------------------+ >> | nr | types | mid(types,locate('Ac',types),4) | >> +----+----------------+---------------------------------+ >> | 1 | AC01,bc32,45sw | AC01 | >> | 4 | df22,Ac35 | Ac35 | >> | 5 | we23,rd34,Ac33 | Ac33 | >> +----+----------------+---------------------------------+ >> 3 rows in set (0.00 sec) >> >> -- >> Luuk > > Thank you Luuk, but I'm afraid my problem is more hard because lenght > of substring Ac% is not constant (not eqaul 4) than you can make the parameters to the MID-function more complex, OR, you can redesign your database (like Jerry said) -- Luuk |
|
05-29-2008, 10:40 AM
| |||
| |||
| Re: How to get a part of field in select qurey? On 24 Maj, 19:15, Luuk <L...@invalid.lan> wrote: > Andrzej Kubaczyk schreef: > > > > > On 24 Maj, 15:36, Luuk <L...@invalid.lan> wrote: > >> Andrzej Kubaczyk schreef: > > >>> Hello. > >>> In the table I have one column where I storage some data in format: > >>> Ac01,bc32,45sw > >>> Field contains a few part of data coma separated. > >>> +----------------+ > >>> | types | > >>> +----------------+ > >>> | Ac01,bc32,45sw | > >>> | df22,Ac45 | > >>> | dd22 | > >>> | we23,rd34,Ac33 | > >>> +----------------+ > >>> I need to get a part of the field, for example data pass to pattern Ac > >>> % > >>> Demand result is: > >>> Aco1 > >>> Ac45 > >>> Ac33 > >>> Do somebody knows how to do it using mysql function? > >> mysql> select nr, types, mid(types,locate('Ac',types),4) from actypes > >> where types like '%Ac%'; > >> +----+----------------+---------------------------------+ > >> | nr | types | mid(types,locate('Ac',types),4) | > >> +----+----------------+---------------------------------+ > >> | 1 | AC01,bc32,45sw | AC01 | > >> | 4 | df22,Ac35 | Ac35 | > >> | 5 | we23,rd34,Ac33 | Ac33 | > >> +----+----------------+---------------------------------+ > >> 3 rows in set (0.00 sec) > > >> -- > >> Luuk > > > Thank you Luuk, but I'm afraid my problem is more hard because lenght > > of substring Ac% is not constant (not eqaul 4) > > than you can make the parameters to the MID-function more complex, > OR, > you can redesign your database (like Jerry said) > > -- > Luuk Redesign database is impossible. I thing use PHP to extract important data. |
|
05-29-2008, 10:40 AM
| ||||
| ||||
| Re: How to get a part of field in select qurey? Andrzej Kubaczyk schreef: > On 24 Maj, 19:15, Luuk <L...@invalid.lan> wrote: >> Andrzej Kubaczyk schreef: >> >> >> >>> On 24 Maj, 15:36, Luuk <L...@invalid.lan> wrote: >>>> Andrzej Kubaczyk schreef: >>>>> Hello. >>>>> In the table I have one column where I storage some data in format: >>>>> Ac01,bc32,45sw >>>>> Field contains a few part of data coma separated. >>>>> +----------------+ >>>>> | types | >>>>> +----------------+ >>>>> | Ac01,bc32,45sw | >>>>> | df22,Ac45 | >>>>> | dd22 | >>>>> | we23,rd34,Ac33 | >>>>> +----------------+ >>>>> I need to get a part of the field, for example data pass to pattern Ac >>>>> % >>>>> Demand result is: >>>>> Aco1 >>>>> Ac45 >>>>> Ac33 >>>>> Do somebody knows how to do it using mysql function? >>>> mysql> select nr, types, mid(types,locate('Ac',types),4) from actypes >>>> where types like '%Ac%'; >>>> +----+----------------+---------------------------------+ >>>> | nr | types | mid(types,locate('Ac',types),4) | >>>> +----+----------------+---------------------------------+ >>>> | 1 | AC01,bc32,45sw | AC01 | >>>> | 4 | df22,Ac35 | Ac35 | >>>> | 5 | we23,rd34,Ac33 | Ac33 | >>>> +----+----------------+---------------------------------+ >>>> 3 rows in set (0.00 sec) >>>> -- >>>> Luuk >>> Thank you Luuk, but I'm afraid my problem is more hard because lenght >>> of substring Ac% is not constant (not eqaul 4) >> than you can make the parameters to the MID-function more complex, >> OR, >> you can redesign your database (like Jerry said) >> >> -- >> Luuk > > Redesign database is impossible. I thing use PHP to extract important > data. select nr, types, mid(types,locate('Ac',types), locate(',',concat(types,','),locate('Ac',types)+1)-locate('Ac',types)) from actypes where types like '%Ac%'; But a PHP sollution would be more readable (and maintainable)... ;-) -- Luuk |
Linear Mode
