June 18, 2008 at 5:26 pm
Hi i need to present in one gridview some information of one table but i need to filter it before and in ASP.NET/VB.NET i was trying but can't do it...
So i was thinking that with one procedure can be possible too... but my skills in SQL are not so good and i'm just now starting whit it...
So i need one procedure that:
makes one select: select id_word, txt_alt, n_errors from errorwords where n_errors=1 and retrive to out of the procedure all the results of this query but:
for each line returned in the select should analyse the field txt_alt [txt_alt is varchar] and:
if in the string the only upper letter is the first one, this line don't are returned out of the procedure and i won't see it in my gridview
else if there are upper letters, one or more, that are not the first one is returned out of the procedure for i can see it in my gridview
Example:
id_word txt_alt
109 "This line is with only one upper letter"
119 "This haVe two upper letters"
159 "This haVe SeVeral"
So in the procedure it only will be return out the 119 and 159... for i use in my gridview
Thanks for the attention 🙂
June 19, 2008 at 1:45 am
Hello,
if the column txt_alt is defined as case sensitive (see "Collations" in SQL Server Books Online), you can use the function LOWER to find, whether there are some capital letters or not. If you want to ignore the first letter, use SUBSTRING or RIGHT.
/*Column to be checked is Case sensitive (replace collation with respective collation for your language,
or skip it altogether, if your database default is CS)*/
CREATE TABLE #error(id_word int, txt_alt varchar(100) COLLATE Czech_CS_AS)
INSERT INTO #error(id_word, txt_alt)
SELECT 109,'This line is with only one upper letter'
UNION
SELECT 119, 'This haVe two upper letters'
UNION
SELECT 159,'This haVe SeVeral'
UNION
SELECT 999,'this has none'
/*displays only rows that have capital letters inside txt_alt (i.e., other than first letter is capital)*/
SELECT *
FROM #error
WHERE LOWER(RIGHT(txt_alt,LEN(txt_alt)-1)) <> RIGHT(txt_alt,LEN(txt_alt)-1)
Now, if the column you want to check, does not have Case Sensitive collation (most databases use Case Insensitive collation), you have to change collation for the test. This may perform poorly on large tables, but here we go:
/*Column to be checked is Case Insensitive (replace collation with respective collation for your language,
or skip it altogether, if your database default is CI)*/
CREATE TABLE #errors(id_word int, txt_alt varchar(100) COLLATE Czech_CI_AS)
INSERT INTO #errors(id_word, txt_alt)
SELECT 109,'This line is with only one upper letter'
UNION
SELECT 119, 'This haVe two upper letters'
UNION
SELECT 159,'This haVe SeVeral'
UNION
SELECT 999,'this has none'
/*inner query just changes the collation, outer query selects rows with capital letters inside the string*/
SELECT *
FROM
(SELECT id_word, txt_alt COLLATE Czech_CS_AS as txt_CS
FROM #errors) as Q
WHERE LOWER(RIGHT(txt_CS,LEN(txt_CS)-1)) <> RIGHT(txt_CS,LEN(txt_CS)-1)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply