Combined wildcard and between statements

Question

Combined wildcard and between statements

steviemoxford

Valued Member

Points: 74
More actions
May 31, 2012 at 4:32 am

#392393

I'm trying to write a formula so it basically brings back all the primary diagnosis fields (hospital data) except the following shown below - The problem I'm having is combining both a wildcard (like) and 'between' statements.
example;
DIAG1 (Principal Diagnosis) not between C00* - C97* OR not between D37* - D48*
Please hep 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

I'm trying to write a formula so it basically brings back all the primary diagnosis fields (hospital data) except the following shown below - The problem I'm having is combining both a wildcard (like) and 'between' statements.
example;
DIAG1 (Principal Diagnosis) not between C00* - C97* OR not between D37* - D48*

Your C* and D* fields are not integers and cannot be incremented or compared against as if they are - so your BETWEEN won't work. You could unpick the string instead by separating out the letter and number (check for CAST and CONVERT in BOL) and doing your BETWEEN operation then.

Also don't forget (brackets), and that NOT will take precedence over OR.

EDIT: Can you post the table structure please? I'll work on an example for you.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

steviemoxford Valued Member Points: 74 More actions · Answer 2

That's really kind of you - I've attached a copy of the table structure.

Cheers

Steve

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

steviemoxford (5/31/2012)
That's really kind of you - I've attached a copy of the table structure.
Cheers
Steve

What would be far better than a spreadsheet is to post ddl (create table statements) and sample data (insert statements) then desired output based on your sample data. Take a look at the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

aureolin Hall of Fame Points: 3520 More actions · Answer 4

You could do something like:

Where left(Diag1,3) > 'C00' and left(Diag1,3) < 'C97' ...

or you could create a CTE that extracts the left 3 characters of your diagnosis code (please forgive any syntax errors, I'm doing this from memory):

with codes(diag_code)

as (select left(diag1,3) as diag_code from .... )

...

Where codes.diag_code between 'C00' and 'C97' ...

Caveat: Note that YMMV depending on your data and schema.

If you regularly use the first 3 characters of the diagnosis code, you might consider creating a view, and giving things clear and understandable names.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

aureolin (6/1/2012)
You could do something like:
Where left(Diag1,3) > 'C00' and left(Diag1,3) < 'C97' ...

This would be non-sargable so performance could very easily become a big issue.

or you could create a CTE that extracts the left 3 characters of your diagnosis code (please forgive any syntax errors, I'm doing this from memory):
with codes(diag_code)
as (select left(diag1,3) as diag_code from .... )
...
Where codes.diag_code between 'C00' and 'C97' ...
Caveat: Note that YMMV depending on your data and schema.
If you regularly use the first 3 characters of the diagnosis code, you might consider creating a view, and giving things clear and understandable names.

This is not too bad. It is at least sargable.

It is hard to say what the best approach but actually just using string comparisons will probably work fine if the actual data is like the brief sample posted.

Just make your where clause something like this:

where DIAG1 not between 'C00' and 'C97'

AND DIAG1 not between 'D37' and 'D48'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

steviemoxford (5/31/2012)
I'm trying to write a formula so it basically brings back all the primary diagnosis fields (hospital data) except the following shown below - The problem I'm having is combining both a wildcard (like) and 'between' statements.
example;
DIAG1 (Principal Diagnosis) not between C00* - C97* OR not between D37* - D48*
Please hep 🙂

I'm having a bit of a problem figuring out your meaning with the BETWEEN. This is where posting DDL, sample data, and expected results really helps. Part of this means making sure your sample data includes data that will be excluded from the results set.

ScottPletcher SSC Guru Points: 101093 More actions · Answer 7

I think this will do it:

WHERE

DIAG1 NOT LIKE 'C[0-8][0-9]%' AND

DIAG1 NOT LIKE 'C9[0-7]%' AND

DIAG1 NOT LIKE 'D3[7-9]%' AND

DIAG1 NOT LIKE 'D4[0-8]%'

Or, some people prefer this style:

WHERE NOT (

DIAG1 LIKE 'C[0-8][0-9]%' OR

DIAG1 LIKE 'C9[0-7]%' OR

DIAG1 LIKE 'D3[7-9]%' OR

DIAG1 LIKE 'D4[0-8]%' )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Paul White SSC Guru Points: 150467 More actions · Answer 8

steviemoxford (5/31/2012)
I'm trying to write a formula so it basically brings back all the primary diagnosis fields (hospital data) except the following shown below - The problem I'm having is combining both a wildcard (like) and 'between' statements. DIAG1 (Principal Diagnosis) not between C00* - C97* OR not between D37* - D48*

Can you not join to an ICD-10 reference table and exclude neoplasms? That would seem to be a more robust and readable solution than trying to write complex NOT LIKE BETWEEN statements.

http://en.wikipedia.org/wiki/ICD-10_Chapter_II:_Neoplasms

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi