Solved

more than 4 upper case characters in a row

Posted on 2013-12-12
7
334 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
ID: 39714828
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 69

Expert Comment

by:Qlemo
ID: 39715448
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
ID: 39716758
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 69

Expert Comment

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

Author Comment

by:jvoconnell
ID: 39716921
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 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39717003
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
ID: 39717040
Genius! This site is the best by far.
Thank you both for your efforts.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Loop to go backward 90 days 2 18
Sql server function help 15 27
Sql Server group by 10 23
SQL view 2 26
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

816 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