Find Capital Letter Position with PatIndex (T-SQL)


--1.Find first Capital letter position:
SELECT patindex('%[A-Z]%'  ,'where is the Capital Letter'  COLLATE Latin1_General_BIN)

--Or use different collation:
SELECT patindex('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%','where is the Capital Letter'  COLLATE SQL_Latin1_General_CP1_CS_AS)


--2.Find all capital letter position
-- We can use  either  collation: Latin1_General_BIN or SQL_Latin1_General_CP1_CS_AS to change case sensitive level

;with mycte as (
Select 65 as i, char(65) as Capitalletter
Union all
Select i+1 i, char(i+1) from mycte
WHERE i<90)

SELECT Capitalletter, patindex('%'+Capitalletter +'%','where is the Capital Letter' COLLATE SQL_Latin1_General_CP1_CS_AS)
FROM mycte 
/*
Capitalletter	pos
A	0
B	0
C	14
D	0
E	0
F	0
G	0
H	0
I	0
J	0
K	0
L	22
M	0
N	0
O	0
P	0
Q	0
R	0
S	0
T	0
U	0
V	0
W	0
X	0
Y	0
Z	0

*/


--http://www.techonthenet.com/ascii/chart.php
--http://technet.microsoft.com/en-us/library/ms187323.aspx



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s