It is common scenario that, we might need to extract the data from the SQL server based on some pattern. For example extract all customers information who has a valid PAN card number (XXXXX0000X). SQL server is not very powerful in pattern matching.We can easily implement simple pattern matching but for complicated one we might need to used Regular Expression using CLR integration. In this post let us discuss about the possibilities of pattern matching using SQL server syntax.
To fetch all customers who has valid PAN card number (5
Alphabet 4 numeric 1 Alphabet), we can use the following query.
SELECT * FROM customers
WHERE Pancard LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][A-Z]'
To fetch all customer, whose postal code does not have any alphabet.SELECT * FROM customers
WHERE PostalCode
NOT LIKE '%[A-Z]%'To fetch all customer, whose postal code is alpha numeric
SELECT * FROM customers
WHERE PostalCode
LIKE '%[A-Z]%' AND PostalCode
LIKE '%[0-9]%' To fetch all customer, whose first character of postal code is not vowels
SELECT * FROM customers
WHERE PostalCode
LIKE '[^aeiou]%' To fetch all customer, whose has postal code does not contain special characters @ ,# ,$ and %
SELECT * FROM customers
WHERE
PostalCode
NOT LIKE '%[@#$%]%' To fetch all customers, whose postal code contain the character '%'. We have to use escape character as '%' used for wild card search.
SELECT * FROM customers
WHERE
PostalCode
LIKE '%@%%' ESCAPE '@'To fetch all customers, whose postal code starts with anything but second character is A to D
SELECT * FROM customers
WHERE
PostalCode
LIKE '_[A-D]%'These are the common pattern search option available in SQL server . We can mix and match this to make more complicated pattern search.
If you liked this post, do like my page on FaceBook
Usefull.
ReplyDeleteLondon has many natural strengths. Beyond its recognition as a international economic center the metropolis is the clean worldwide leader especially https://www.gethomeworkonline.com/ regions which include forex shopping for and selling bond buying and selling circulate border financial organization lending.
DeleteGreat Article
DeleteFinal Year Projects for CSE
IEEE Projects for Engineering Students
JavaScript Training in Chennai
JavaScript Training in Chennai
Very useful. Thanks.
ReplyDeletegood
ReplyDeleteI want to pattern match "return" and space and "numeric value"
ReplyDeletelike return 99,return 200.
I tried like '%return_[0-9]%'
and it didnot work
ReplyDeleteVery much useful.Many Many thanks!!!!
ReplyDeletehttp://khairyayman.eklablog.com/http-emc-mee-com-transfer-furniture-almadina-almonawara-html-a126376958 شركة نقل عفش بالمدينة المنورة
ReplyDeletehttp://khairyayman.eklablog.com/http-emc-mee-com-transfer-furniture-jeddah-html-a126377054 شركة نقل عفش بجدة
http://khairyayman.eklablog.com/http-emc-mee-com-movers-in-riyadh-company-html-a126376966 شركة نقل عفش بالرياض
http://khairyayman.eklablog.com/http-www-east-eldmam-com-a126377148 شركة نقل عفش بالدمام
https://forums.ipa.edu.sa/vb4/member.php?202-%D9%85%D8%AD%D9%85%D8%AF-%D8%AE%D9%8A%D8%B1%D9%8A
ReplyDeleteنقل عفش شركة نقل عفش
فني صحي الكويت فني صحي
mac keyboard shortcuts
ReplyDeleteapps for iphone
how to hid iphone apps
how to clear cache cookies on ipad
install bootlace
jailbreak ps4
jailbreak ios 11
jailbreak ios 10 tutorial
This comment has been removed by the author.
ReplyDelete나누미 검증 추천 | 바둑이사이트 현금바둑이 딱 2곳만 추천 가장 안전하고 정직하며 신속한 바둑이사이트를 메이저 중에서도 메이저로 만나실 수 있습니다 한국을 대표하는 바둑이게임 더 이상의 바둑이사이튼 절대 없습니다 먹튀검증사이트 나누미 추천은 정확합니다 바둑이게임.
ReplyDeleteThese are the common pattern search option available in SQL server . We can mix and match this to make more complicated pattern search. Afghani Round Ear Ring , Coins Head Piece , wooden jewelry box , round neck scarf
ReplyDelete