Solved

more than 4 upper case characters in a row

Posted on 2013-12-12
7
326 Views
Last Modified: 2013-12-13
Experts,

For SQL Server 2012 -- if SQL Server version matters in this case.

I have a unique situation where I need to search a field to check for instance where there are more that 3 upper case characters in a row.

If the value in the column is MedicalRecordCD, I wouldn't want that.
If the value in the column is PatientPLNNumber, I would want that to be returned.

Any assistance is appreciated.
0
Comment
Question by:jvoconnell
  • 3
  • 3
7 Comments
 
LVL 19

Expert Comment

by:jss1199
Comment Utility
There is no function within SQL to count uppercase letters.  You can create one using the below code.

You could then do WHERE dbo.CountUpperCase (PatientPLNNumber) > 4

CREATE FUNCTION CountUpperCase
(
    @input nvarchar(50)
)
RETURNS int
AS
BEGIN

    declare @len int
    declare @i int
    declare @count int
    declare @ascii int

    set @len = len(@input)
    set @i = 1
    set @count = 0

    while @i <= @len
    begin

        set @ascii = ascii(substring(@input, @i, 1))

        if @ascii >= 65 and @ascii <= 90
        begin
            set @count = @count +1
        end

        set @i = @i + 1

    end

    return @count

END

Open in new window

0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Like could be used, too:
... where field collate Latin1_General_CS_AS like '%[A-Z][A-Z][A-Z][A-Z]%'

Open in new window

0
 
LVL 1

Author Comment

by:jvoconnell
Comment Utility
Thank you for your assistance. Looking back at my post, I think I definatley could have been clearer...and I apologize.
This fn is close. It does give a count of all upper case characters within the field. I need it to return those where there are >= 4 Consecutive upper case characters

So 'MedicalRecordCD' would not be valid as the 4 ucase are not consecutive
PatientPLNNumber would be valid as it does have 4 consecutive ucase chars "PLNN"

Once again, I apologize for putting you down the wrong path.
I will see if I am able to modify this function to get it to return the desired output.

Thank you.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Did you try the LIKE approach? It should check exactly for that.
0
 
LVL 1

Author Comment

by:jvoconnell
Comment Utility
Hello, I did try a variation of both suggestions.


SELECT DISTINCT ColumnNM
FROM ColumnBASE
WHERE CountUpperCase(COLUMNNM) > 3
AND SourceSystemNM = 'PAYERSTAGE'
and COLUMNNM collate Latin1_General_CS_AS like '%[A-Z][A-Z][A-Z][A-Z]%'


SELECT DISTINCT ColumnNM
FROM ColumnBASE
WHERE  SourceSystemNM = 'PAYERSTAGE'
and COLUMNNM collate Latin1_General_CS_AS like '%[A-Z][A-Z][A-Z][A-Z]%'

I am still getting values back  such as   --> ActualCostAMT

...side note. I know this request may sound somewhat foolish. The background is we are implementing a new DW and there are strict naming conventions we have been asked to follow.
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
That was supposed to work, but obviously the sort order still gets in the way. If we just do Binary compare, it works:
SELECT DISTINCT ColumnNM
FROM ColumnBASE
WHERE  SourceSystemNM = 'PAYERSTAGE'
and COLUMNNM collate Latin1_General_BIN like '%[A-Z][A-Z][A-Z][A-Z]%'

Open in new window

0
 
LVL 1

Author Closing Comment

by:jvoconnell
Comment Utility
Genius! This site is the best by far.
Thank you both for your efforts.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now