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.

19 comments:

Anonymous said...

Michael Jabroni is a good guy

Unknown said...

super ji..thanks a lott

Unknown 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.

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

Unknown 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/

Unknown 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...

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

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

Unknown 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


Unknown 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

caiyan said...

polo ralph lauren outle
ray ban sunglasses cheap
adidas nmd r1
michael kors outlet store
rolex replica watches for sale
michael kors uk
pandora jewellery
fit flops
kate spade
ralph lauren uk
0413shizhong

Unknown said...

nike air max online
north face jackets
prada handbags
ralph lauren outlet
ray ban sunglasses sale
ysl bags
nike huarache black
oakley sunglasses wholesale
christian louboutin sale
lebron 13 shoes
2017.6.27chenlixiang

Unknown said...

adidas outlet
nike air max 90
gucci outlet
ugg boots
timberland boots
nike store
pandora jewelry
yeezy boost 350
fitflops shoes
polo outlet
mt0718

Unknown said...

adidas tubular shadow
jordan shoes
adidas superstar shoes
longchamp le pliage
nike air max 2017
burberry scarf
chrome hearts
cheap basketball shoes
lebron james shoes
lacoste online shop

Unknown said...

nhl jerseys
michael kors outlet
cheap ray ban sunglasses
versace jeans
michael kors handbags
prada outlet
ugg boots
ugg outlet
kate spade sale
michael kors uk

Unknown said...

20180412xiaoke
birkenstock outlet
louis vuitton outlet store
pandora charms sale clearance
michael kors handbags
pandora outlet
replica rolex
ugg outlet
ugg boots clearance
christian louboutin shoes
oakley sunglasses sale

zzyytt said...

lacoste outlet
nike x off white
rayban sunglasses
yeezy boost 350 v2
nike air max
paul george shoes
longchamp
oakley sunglasses
yeezy boost
gucci belts

5689 said...

zzzzz2018.11.6
ugg boots clearance
canadian goose
pandora charms
ugg boots clearance
pandora outlet
pandora outlet
ralph lauren uk
mbt shoes
louboutin shoes
nike outlet