Wednesday, December 1, 2010

MySQL's FIND_IN_SET()

I recently discovered a neat function in MySql called find_in_set(str, strlist). It basically takes an argument X as your string, and searches the inputed string list.

Say for example you have a column in your table named "country" and it stores the country a specific person visited to separated by a comma (,). Sample table below:

Table: Person
|   Name                   |    Country                            |
|   Michael Jabroni    |    USA, Brazil, Japan           |

To use find_in_set() now you can have a select that looks like this:
SELECT * FROM Person WHERE FIND_IN_SET('Brazil', Country)

Seemed useless right because why not use LIKE '%Brazil%' instead.
Say instead of having comma as a delimiter someone used a colon (:) instead for the database.

Table: Person
|   Name                   |    Country                                                       |
|   Michael Jabroni    |    USA:Japan:Africa:China:South Africa           |
|   John Maldoe        |    China:South Africa                                       |

(I know Africa is not a country,  but for the sake of example.)
Further more, if you want to select a person that ONLY visited Africa, you wont be able to do so because it will also select people that visited South Africa. In addtition, you cannot use find_in_set() because it requires a comma as its delimiter.

One solution you can do is used another MySql string function called REPLACE() with FIND_IN_SET().
A revised select below:
SELECT * FROM Person WHERE FIND_IN_SET( 'Africa', REPLACE(Country, ':', ',') )

This will replace all instance of a collon (:) to a comma(,).
Now you can select only people who visited Africa.

31 comments:

Anonymous said...

Michael Jabroni is a good guy

Sai Santhosh said...

super ji..thanks a lott

Coach Factory said...

moncler outlet, cheap oakley sunglasses, canada goose outlet, oakley outlet, ugg boots, ray ban outlet, oakley sunglasses cheap, ray ban, ray ban outlet, ray-ban, oakley sunglasses outlet, oakley outlet, cheap oakley sunglasses, oakley vault, ray ban sunglasses outlet, canada goose, oakley vault, louboutin, tn pas cher, cheap oakley sunglasses, ray ban, canada goose, ray ban, ugg, oakley sunglasses outlet, ray bans, raybans, canada goose, oakley vault, ray-ban, oakley outlet, cheap sunglasses, ralph lauren, rayban, cheap oakley, moncler, oakley sunglasses cheap, gucci, oakley sunglasses outlet, moncler, oakley, canada goose, cheap sunglasses, ray bans, cheap sunglasses, oakley sunglasses cheap, moncler, canada goose outlet, rayban, discount oakley sunglasses

Coach Factory said...

jordan shoes, louboutin, michael kors outlet, michael kors, michael kors, louis vuitton handbags, nike air max, louis vuitton, gucci outlet, true religion outlet, michael kors outlet, air max, coach outlet store, ray ban sunglasses, burberry outlet online, michael kors, longchamp outlet, nike free, true religion jeans, burberry outlet online, coach factory outlet, louboutin, toms shoes, air jordan, polo ralph lauren, chanel handbags, ray ban, prada outlet, tiffany and co, nike air max, polo ralph lauren outlet, louboutin, coach outlet, nike shoes, ralph lauren, kate spade outlet, tory burch outlet, air max, oakley sunglasses, oakley sunglasses, true religion, michael kors outlet, oakley sunglasses, longchamp, prada handbags, nike free, coach purses, coach factory,

Coach Factory said...

celine handbags, chi flat iron, nike air max, nfl jerseys, ugg boots, lululemon, mcm handbags, marc jacobs, supra shoes, soccer shoes, lancel, beats by dre, vans, p90x3, ghd, herve leger, nike free, ferragamo shoes, burberry, birkin bag, juicy couture outlet, montre pas cher, soccer jerseys, ralph lauren, hermes, north face outlet, ugg boots, valentino shoes, louis vuitton, jimmy choo, new balance shoes, mulberry, rolex watches, louis vuitton, abercrombie and fitch, oakley pas cher, reebok outlet, hollister, air max, mont blanc, bottega veneta, converse shoes, ray ban pas cher, karen millen, wedding dresses, hogan, yoga pants, insanity, asics gel, timberland

co wendy said...

Sergei , October 22, 1970 Born in Christian Louboutin Bois Dore Moscow State Ozherelye. Perhaps influenced by Cheap LV Handbags their parents worship warrior army, join the army in cheap jordans 1989, and into the elite airborne military service. 1991, by the influence of the Soviet Union, the uggs on sale Russian christian louboutin army general lack of funds, a large number ugg australia of Air Jordan 11 Gamma Blue soldiers seeking veterans, but under extremely difficult conditions still dedicated, praise superiors, and soon was sent to the famous christian louboutin shoes Ryazan Higher ugg Airborne Command School studies. After graduating in 1994, entered the famous Pskov 76th Guards Airborne discount christian louboutin Division, served as discount nike jordans the reconnaissance platoon, reconnaissance ugg soldes deputy company commander, company commander, battalion airborne regiment scouting director and other duties.From 2000 to Cheap Louis Vuitton Handbags 2004, where the forces have repeatedly ordered war with Chechen militants. Because of their opponents in order to form the squad activity and haunted impermanence, the Russians had to ugg boots mobilize elite troops set up large uggs outlet number of small units, "a Discount Louis Vuitton small wholesale jordan shoes play small" Implementation siege. commanding troops in Chechnya, Christian Louboutin Daffodile Ingushetia and other places over the mountains, to track the militants fled. Discount LV Handbags Although the militants were extremely vigilant, but as long as the task has never had outsmarted.August 8, 2008, Georgia suddenly invade South Ossetia (Russian peacekeepers stationed ugg pas cher there), the Russian military to respond quickly, including including the 76th Guards Airborne Division, more than christian louboutin remise 50% 3,000 people were immediately delivered to the pro-Russian Abkhazia, Georgia cheap nike jordan shoes Army contain two infantry brigades, effectively coordinate the cheap christian louboutin direction of the Russian Bags Louis Vuitton troops in South Ossetia.

Guo Guo said...

yao20150324
michael kors handbags
thomas sabo
valentino shoes
ralph lauren outlet
mac cosmetics
true religion outlet
tods outlet
adidas wings
iphone 6 cases
thomas sabo uk
hollister clothing
ray ban uk
ralph lauren outlet
abercrombie
cheap oakley sunglasses
mont blanc
hollister shirts
burberry outlet online
burberry handbags
replica watches
converse shoes
ghd
tods outlet
toms shoes
lacoste shirts
michael kors outlet online
michael kors handbags
louis vuitton outlet
ferragamo shoes
soccer jerseys
michael kors outlet
ray ban sunglasses

Unknown said...

new york giants jerseys
juicy couture outlet
chanel outlet
ray ban sunglasses
robert griffin jersey,joe theismann jersey,andre roberts jersey,sonny jurgensen jersey,art monk jersey,bashaud breeland jersey,barry cofield jersey,perry riley jersey,e.j. biggers jersey,duke ihenacho jersey,josh morgan jersey
mizuno shoes
seahawks jersey
stuart weitzman sale
nike free,nike free run,free running,free run,nike running
five fingers shoes
tory burch outlet online
uggs outlet
cheap oakley sunglasses
cheap jordans
mizuno running shoes
derrick rose jersey
cowboys jerseys
barcelona soccer jersey
bottega veneta wallet
nike roshe
chanel outlet,chanel handbags,chanel bags,chanel outlet,chanel 2.55
supra shoes
giants jersey
stephen curry jersey
miami dolphins jerseys
bottega veneta outlet
indianapolis colts jerseys
nike air max 2015
ray ban outlet
arizona cardinals jerseys
the north face clearance
coach outlet store
nike trainers
chelsea jersey
washington redskins jerseys
zhuweiming0627

chenlina said...

chenlina20150711
ray ban sunglasses
imitation watches
louis vuitton handbags
abercrombie store
chanel bags
chanel handbags
air max 90
soccer cleats
louis vuitton
coach outlet
michael kors uk
concords 11
chanel outlet
oakley sunglasses
gucci handbags
true religion sale
hollister clothing
cheap chanel handbags
insanity dvd
lebron 11
michael kors uk
louboutin
fendi handbags
louis vuitton handbags
louis vuitton
coach outlet store online
longchamp outlet
toms shoes
hollister
ray ban glasses
cheap jerseys wholesale
mulberry handbags
chanel outlet
louis vuitton outlet
coach outlet online
fitflop sale
michael kors outlet
ray ban outlet
louboutin
michael kors handbags

Hua Cai said...

the north face clearance
pandora jewelry
salomon running shoes
cheap ray ban sunglasses
tory burch outlet online
new york giants jerseys
toms outlet
cheap soccer jerseys
adidas outlet
lacoste polo shirts
pandora jewelry
chicago bears jerseys
cheap oakley sunglasses
air max 90
lacoste outlet
hollister
ray ban sunglasses
mulberry outlet
michael kors outlet online
stuart weitzman boots
oakley sunglasses
miami heat jersey
links of london
kate spade outlet
air force one shoes
burberry handbags
koby bryant shoes
manchester united jersey
air max 2015
denver broncos jerseys
polo ralph lauren outlet
christian louboutin outlet
the north face outlet
hollister shirts
valentino shoes
hollister clothing
chanel outlet
mcm bags
nike roshe
true religion jeans
2015717caihuali

Gege Dai said...


15.07.20fangyanting
replica watches rolex
oakley store
soccer outlet
nike air force pas cher
burberry outlet online
true religion sale
ralph lauren uk outlet
prada shoes
michael kors handbags
michael kors handbags
polo ralph lauren uk
cheap oakley sunglasses
louboutin
air max uk
toms shoes sale
christian louboutin
cheap jordan shoes
ray ban sunglasses
longchamp
gucci handbags
coach factory outlet
chanel online shop
polo ralph lauren
ray ban outlet
coach outlet online
pandora jewelry stores
tory burch shoes
retro jordans
pandora jewelry outlet
nike blazer high
coach factory outlet
kate spade
nike air max uk
cheap ray ban sunglasses
ray ban sunglasses

Zheng junxai5 said...

2015-09-16 zhengjx
Michael Kors Outlet Stores Online
canada goose outlet
Kobe Shoes,Kobe 9,Kobe 8 Discount
Coach Factory Outlet Official Coach Handbags Sale
ray-ban sunglasses
Louis Vuitton Handbags Outlet Online
michael kors bag
Coach Outlet Online Coach Factory Outlet
Louis Vuitton Handbags UK For Sale
Louis Vuitton Handbags Official USA Website
timberland boots
ugg boots
Oakley Sunglasses Official Website Cheap Off
coach factory outlet online
nike trainers
toms outlet
celine handbags
michael kors outlet
Hollister UK Clothing Store
Louis Vuitton Outlet Discount Handbags
Cheap Louis Vuitton Handbags Luggage Bags Sale
cheap jordans
hollister uk
michael kors bags
ugg boots sale
Gucci Outlet Online 80% OFF
ugg australia
michael kors outlet
Mont Blanc Pens For Sale
Michael Kors Outlet Online 70% Off Handbags

Wenhao Guo said...

cheng0928
louis vuitton handbags, http://www.louisvuittonhandbag.us/
hermes birkin, http://www.hermesbirkin.org/
mbt shoes, http://www.mbtshoesoutlet.us.com/
swarovski outlet, http://www.swarovski-outlet.co.uk/
hogan, http://www.scarpehogan-outlet.it/
ray ban, http://www.occhiali-rayban.it/
nfl jerseys wholesale, http://www.nfljerseys-wholesale.us.com/
nba jerseys, http://www.nbajerseys.us.com/
longchamp handbags, http://www.longchamphandbag.us.com/
cheap nhl jerseys, http://www.nhljerseys.us.com/
cheap mlb jerseys, http://www.cheapmlbjerseys.net/
north face jackets, http://www.thenorthfaces.in.net/
cheap snapbacks, http://www.cheapsnapbacks.us.com/
adidas wings, http://www.adidaswings.net/
basketball shoes, http://www.basketballshoes.us.com/
lebron james shoes, http://www.lebronjamesshoes.in.net/
lacoste polo shirts, http://www.lacostepoloshirts.cc/
vans shoes, http://www.vans-shoes.cc/
ugg boots, http://www.uggboot.com.co/
hermes belt, http://www.hermesbelts.us/

mao qiuyun said...

ray ban sunglasses, http://www.raybansunglassesonline.us.com/
nike trainers, http://www.niketrainers.me.uk/
gucci handbags, http://www.guccihandbags-outlet.co.uk/
herve leger, http://www.herveleger.us.com/
timberland boots, http://www.timberlandboots.name/
coach outlet online, http://www.coachoutletonline-store.us.com/
michael kors outlet, http://www.michaelkorsoutletcanada.in.net/
juicy couture outlet, http://www.juicycoutureoutlet.net/
calvin klein underwear, http://www.calvinklein.in.net/
ray ban sunglasses, http://www.raybansunglass.us.com/
prada outlet, http://www.pradaoutlet.us/
lululemon outlet, http://www.lululemonoutletstore.in.net/
tory burch shoes, http://www.toryburchshoesoutlet.com/
michael kors outlet, http://www.michaelkorsoutlet.org.uk/
replica watches, http://www.replicawatchesforsale.us.com/
pandora jewelry, http://www.pandorajewelryoutlet.us.com/
nfl jerseys wholesale, http://www.nfljerseys-wholesale.us.com/
michael kors outlet, http://www.michaelkorsoutletonlinstore.us.com/
oakley outlet, http://www.oakleyoutlet.in.net/
oakley sunglasses, http://www.oakleysunglasses-outlet.us.com/
michael kors bags, http://www.michaelkorsbags.uk/
ray ban sunglasses, http://www.ray-bansunglasses.eu.com/
oakley sunglasses, http://www.oakley.in.net/
air max 2015, http://www.airmax2015.in.net/
the north face uk, http://www.thenorthfaces.org.uk/
swarovski outlet, http://www.swarovski-outlet.co.uk/
pandora jewelry, http://www.pandora.eu.com/
chanel handbags, http://www.chanelhandbags-outlet.co.uk/
1002maoqiuyun

chenlina said...

chenlina20151127
ugg boots sale
oakley sunglasses
louis vuitton handbags
ray ban sunglasses outlet
chanel handbags
air force 1
christian louboutin shoes
pandora outlet
the north face outlet
cheap air max
oakley sunglasses
jordan retro
nike free runs
uggs outlet
oakley outlet
jordans for sale
ugg boots outlet
christian louboutin outlet
the north face outlet
louis vuitton purses
louis vuitton outlet
jordan concords
louis vuitton outlet
louis vuitton outlet
ugg boots
uggs on sale
ugg boots
louis vuitton
louboutin pas cher
christian louboutin
coach factory outlet
gucci outlet
ugg slippers
running shoes
swarovski outlet
coach outlet store online
official coach factory outlet
uggs outlet
air jordans
canada goose outlet
as

方松腾 said...

bottega veneta outlet online
burberry outlet
cheap toms
polo ralph lauren outlet
coach outlet store online,coach outlet store,coach outlet
nba jerseys wholesale
moncler outlet
ugg boots clearance
valentino shoes
michael kors factory sale
rolex watches
kobe bryants shoes 2015
mont blanc
true religion jeans,cheap true religion jeans,true religion jeans wholesale,true religion outlet,true religion sale,true religion outlet online
ray-ban sunglasses
ysl outlet online
louis vuitton outlet
lebron james shoes,lebron 12,lebron 12 shoes,lebron sneakers,lebron iv,lebron 11,lebron 10
hermes outlet online
cheap oakley sunglasses
celine outlet
cleveland cavaliers
football shirts uk,soccer jerseys uk,cheap soccer jerseys uk
ferragamo shoes
pandora charms
ralph lauren,ralph lauren uk,ralph lauren outlet,ralph lauren outlet online,polo ralph lauren outlet,ralph lauren polo
omega watches
michael kors uk outlet
burberry outlet
christian louboutin

chenlina said...

chenlina20160127
michael kors outlet
burberry outlet
chanel handbags
ralph lauren outlet
cheap jordans
beats solo
ugg boots sale
louis vuitton outlet
true religion outlet
gucci handbags
cheap oakley sunglasses
louboutin
air max 95
chanel outlet
mihchael kors outlet online
jordan 11 concord
louis vuitton handbags
gucci outlet
kate spade
ray ban sunglasses
toms shoes
uggs outlet
ray ban sunglasses
nike air max
michael kors handbags
coach outlet store online
oakley sunglasses
coach factory outlet
louis vuitton purses
canada goose jackets
louis vuitton
replica watches
michael kors outlet
louis vuitton outlet stores
ray ban outlet store
marc jacobs handbags
louis vuitton
louboutin pas cher
tiffany and co
cheap oakley sunglasses
as

Yuanyuan Lin said...

6.29llllllyuanoakley sunglasses wholesale
louis vuitton handbags outlet
ray-ban sunglasses
michael kors wallet
ray ban sunglasses
oakley sunglasses wholesale
longchamp handbags
juicy couture tracksuit
prada sunglasses for women
swarovski outlet
ralph lauren polo
longchamp outlet online
links of london
cartier watches
nike roshe run
asics
ferragamo outlet
cheap ray ban sunglasses
polo ralph lauren
michael kors outlet
nike tn pas cher
ray ban sunglasses
tiffany and co
tory burch outlet online
michael kors outlet
mulberry outlet
tiffany jewellery
tory burch outlet
babyliss pro
police sunglasses for men
prada outlet online
soccer jerseys
michael kors clearance
mcm outlet
true religion jeans outlet
6.29

John said...

louboutin shoes
nike air force black
adidas superstar
louis vuitton borse
kate spade outlet online
stephen curry shoes
dolce and gabbana outlet online
michael kors bags
polo ralph lauren outlet online
timberland shoes
cheap nfl jerseys
ralph lauren outlet
air max 90 black
michael kors handbags
nike air max
valentino shoes
nike free run flyknit
tiffany jewelry
fitflops shoes
gucci outlet
ray ban sunglasses
louis vuitton handbags
converse shoes
birkenstock uk
polo ralph lauren outlet
true religion jeans outlet
louis vuitton outlet online
nike free runs
pandora charms sale clearance
converse
nba jerseys wholesale
hermes uk
cheap oakleys outlet
nike air force
michael kors outlet
oakley sunglasses
2016630yuanyuan

Unknown said...

air max 90
louis vuitton handbags
air jordan 11
nike store uk
michael kors wallet
north face jacket
converse shoes
ralph lauren
reebok trainers
louis vuitton
toms shoes
canada goose jackets
mcm outlet
lululemon outlet
michael kors outlet
ralph lauren polo
swarovski crystal
burberry outlet
michael kors handbags
polo ralph lauren
louis vuitton
hollister canada
ugg boots
polo ralph lauren
tiffany and co
ralph lauren uk
rolex watches
celine outlet
rolex watches for sale
coach outlet clearance
polo ralph lauren
coach outlet store
true religion sale
longchamp handbags
michael kors outlet
chanyuan0913

xumeiqing said...

2016923meiqing
yeezy boost 350
jordan shoes
polo ralph lauren outlet online
true religion outlet
fitflops sale clearance
michael kors outlet online
gucci handbags
red bottoms outlet online 
fit flops
pandora jewelry

兮兮 said...

20161021 junda
oakley sunglasses
coach outlet canada
uggs
coach outlet
ferragamo shoes
air jordan shoes
canada goose outlet
swarovski crystal
coach outlet
uggs clearance

dong dong23 said...


uggs outlet
seattle seahawks jerseys
adidas yeezy 350
ugg slippers
ray ban sunglasses
cheap uggs
pittsburgh steelers jerseys
uggs boots
oakley vault
ray ban sunglasses
louis vuitton outlet
cheap ray ban sunglasses
michael kors outlet
true religion uk
coach outlet online
coach outlet
fitflop shoes
ralph lauren polo
cheap jordans
christian louboutin
louis vuitton outlet
true religion outlet
uggs for women
cheap jordans
canada goose jackets
ray ban glasses
coach outlet
sac longchamp
pandora jewelry
rolex watches
basketball shoes
toms shoes
coach outlet online
ray ban outlet
ray ban wayfarer
oakley sunglasses
christian louboutin uk
canada goose
ugg boots uk
moncler jackets
201611.5wengdongdong


roshini said...


Sage Support Phone Number
Sage 50 Tech Support Phone Number
Sage 50 Support Number
Sage 50 Technical Support Phone Number
Sage 50 Tech Support Number
Sage 50 Technical Support Phone Number
Sage 50 Technical Support Number
Sage 50 Customer Support Phone Number
Sage 50 Customer Service Number

Soni yadav said...

Aol Technical Support Phone Number
Aol Technical Support Number
Aol Tech Support Number
Aol Tech Support phone Number
Aol Support Phone Number
Aol Tech Support Number
Outlook Technical Support Phone Number
Outlook Technical Support Number
Outlook Tech Support Number
Outlook Tech Support Phone Number
Outlook Support Phone Number
Outlook Tech Support Number

Anjilo Jilo said...


Quicken Support Phone Number
Quicken Support Phone Number
Quicken Support Number
Quicken Tech Support Number
Quicken Tech Support Phone Number
Quicken Technical Support Number
Quicken Technical Support Phone Number
Quicken Technical Support Number
Quicken Tech Support Phone Number
Quicken Tech Support Number

mohan kumar said...

Quickbooks Support Phone Number
Quickbooks Payroll Support Phone Number
Quickbooks Payroll Tech Support Number
Quickbooks Payroll Support Number
Quickbooks Payroll Technical Support Number
Quickbooks Payroll Tech Support Phone Number
Quickbooks Payroll Technical Support Phone Number
Quickbooks Payroll Help Desk Phone Number
Quickbooks Payroll Help Desk Number
Quickbooks Payroll Customer Service Number
Quickbooks Payroll Customer Service Phone Number
Quickbooks Payroll Customer Support Number
Quickbooks Payroll Customer Support Phone Number

Hua Cai said...

coach outlet
hollister
cheap oakley sunglasses
rolex watches
nike foamposite
ray ban sunglasses
longchamp handbags
cartier watches for sale
ferragamo shoes
coach outlet
20170114caihuali

raybanoutlet001 said...

ralph lauren outlet
chaussure louboutin
longchamp le pliage
coach handbags
new orleans saints jerseys
michael kors handbags
nike trainers uk
cheap michael kors handbags
nike blazer
michael kors handbags

Mohini Roy said...

Quicken Support Number
Quicken Support Phone Number
Quicken Tech Support Number
Quicken Support Number
Quicken Technical Support Number
Quicken Tech Support Phone Number
Quicken Customer Service Number
Quicken Customer Service Phone Number
Quicken Customer Support Number
Quicken Customer Support Phone Number
Quicken Help Desk Phone Number
Quicken Help Desk Number

Mohini Roy said...

Quicken Tech Support Number
Quicken Support Phone Number
Quicken Tech Support Number
Quicken Support Number
Quicken Technical Support Number
Quicken Tech Support Phone Number
Quicken Customer Service Number
Quicken Customer Service Phone Number
Quicken Customer Support Number
Quicken Customer Support Phone Number
Quicken Help Desk Phone Number
Quicken Help Desk Number