SQL Tip: How to Strip Repeating Delimiters from String

Written by Thomas Harlan, Data Repository Technical Team Lead for Reporting Services, Iatric Systems

This is a small tip, but could be quite useful on the days you need it.

Particularly when you work with MEDITECH 6.x PCS data, you will often encounter query response values that look like this:

{|||,30+3/40 Emergency|||||LSCS|Apgar 4/1 7/5}

You can use IatricPiece() to pick out chunks of the delimited data, but you might find too many extra delimiters in place, particularly leading delimiters. So you want to process the string first, to take those out.

Thus IatricStripRepeats()!

Which converts the above string example, via this code:

DECLARE @char char(1) = '|' ;
DECLARE @str varchar(max) = '{|||,30+3/40
Emergency|||||LSCS|Apgar 4/1 7/5}' ;

SELECT zcus.dbo.IatricStripRepeats( @char,@str );

Into...

{|,30+3/40 Emergency|LSCS|Apgar 4/1 7/5}

Which is starting to get you somewhere.

If you need more help…

Our Report Writing team can help you fix reports, create new ones, make old ones faster, and much more. Simply reach out to your Iatric Systems Account Executive or Karen Roemer ([email protected]) to discuss how we can help support your team!

RW-HELP-Button-Blue.png

Topics: Report Writing Services, Data Repository, SQL Tip, Report Writing Tips and Tricks

Subscribe to the Report Writing blog.

Receive the latest articles directly in your inbox.
Enter your email address and click SUBSCRIBE:

Comments

0 COMMENTS

NPR Tip: Custom Dictionary Lookups in NPR Reports

Teach a man to fish… One of my staff needed to create a custom lookup in a MEDITECH NPR report to show the "mapped from" and "mapped to" values for a particular BAR.CLAIM map, and he asked me, “Have...Read More

SQL Tip: Using MEDITECH Date Mnemonics in SSIS

One of many nice things about the MEDITECH system is the availability of date mnemonics that you can use, either when running reports interactively, or when scheduling them. But this functionality is...Read More

SQL Tip: How to Remove MTDD-style Formatting from TextLines

This example is based on how discharge text data is formatted in the MEDITECH MAGIC Data Repository (DR). In the continuing saga of trying to recreate patient discharge instructions from the DR, I...Read More

Summer Webinars: Tips n Tricks, 6.1 Reports Migration, and More

At International MUSE in May, our report writing educational sessions were a hit once again. We presented two sessions: NPR and Report Designer Tips n Tricks 6.1 Reports Migration For some reason,...Read More

PHI and MEDITECH’s Data Repository

To address HIPAA, MEDITECH added logging of access to PHI via reports and all applications, and added that level of detail to MIS user activity logs. So, if you run an NPR, RD, or standard report and...Read More

NPR Tip: Various “Gotchas” in MEDITECH field names

What's in a name? In teaching NPR, RD, and SQL from DR, a big chunk of my time is spent helping students figure out where data is kept (for example, which segment in NPR), and then what field names...Read More

DR Tip: A Dynamic Extract Documentation Template

This blog post is designed for readers who use MEDITECH’s Data Repository. As part and parcel of developing ETL extracts for vendors, it is often quite useful to build a technical specification...Read More

SQL Tip: How to Strip Repeating Delimiters from String

This is a small tip, but could be quite useful on the days you need it. Particularly when you work with MEDITECH 6.x PCS data, you will often encounter query response values that look like this:...Read More

SSRS Tip: Adding a Pipe-delimited option to SSRS Export Destinations

End users are very fond of running reports in SSRS and exporting the results to a variety of formats. Excel is probably the most popular, but invariably there will be other formats needed....Read More

SQL Tip: The Good, the Bad, and the Ugly of TOP X

As you’re learning SQL, you will invariably come across the TOP X clause to a SELECT, which allows you to get – for example – the first 50 rows of a query: SELECT TOP 50 ADV.VisitID FROM...Read More

Migration: Export MOX Using NPR

Recently a MEDITECH hospital wanted to export a handful of MOX databases, since they were migrating away from MOX. I have done a few of these in the past on rare occasions (on a “per database”...Read More

Using DR Data Def Tables, Updated for CS/MAGIC 5.67 or MT 6.07/6.15

One of the challenges of DR-based report development is the same as for NPR or RD … where is the data you want stored? One way to find out is to go into MEDITECH and find the field, and — if you're...Read More

SSIS Tip: Formatting Numbers with Commas via Expression

Oh, Microsoft - why do you make us cry? And no, I am not talking about Windows 10. Though that does make me cry a bit. Every day. But enough about me. You (sound developer that you are) have built a...Read More

SQL+DR Tip: Use SSIS Templates to Speed Extract Builds

If you’ve started using Microsoft’s SQL Server Integration Services (SSIS) toolkit to create, schedule, and deliver extracts (machine-readable files) to vendors, non-MEDITECH applications, and even...Read More

SQL Tip: Managing Diagnosis Codes (5.67 edition)

A common challenge in reporting (or extracting data, in this case) that we see over and over again is the need to transform one data structure into another. The best arrangement of data for the...Read More

NPR Tip: Abstract Status Index (Client/Server)

A MEDITECH 6.0 customer recently requested our help to speed up a report she was using to find all accounts with a final abstract date of T-1. Her report, using the “abs.status.x” index was taking...Read More

MEDITECH DR Tip: Data Dictionary Search with Possessives

Some time ago we published a handy tip providing you with a stored procedure you could use to hunt for things in MEDITECH DR data structure(s) — by table name, by NPR DPM, etc. — so you could get...Read More

MEDITECH SSIS+SQL+DR Tip: Using CMS ICD-10 GEM data

CMS Does Not Make Things Easy Sometimes you just have to wonder… New ICD-10 diagnosis and procedural coding has been brought into play in the United Status (October 1, 2015 for diagnosis codes; next...Read More

NPR Tip: Client/Server “Echo Name” feature

In an earlier blog tip I showed how to add an “Echo Name” feature to a MAGIC NPR report, but I did not show a method to do the same thing for Client/Server because I assumed I’d need to use the...Read More

SQL Tip: Auto-printing from SSRS to a Networked Printer

This is probably our second-most requested tip – how do I emulate, from SSRS, the standard MEDITECH scheduled auto-print to a spooled printer functionality? A Bad Idea Before we show you how to...Read More

Testing Code for ICD-10

October 1 is coming fast, which means that this year (2015) US healthcare finally gets to join the rest of the world in using the ICD-10 codeset for diagnoses. MEDITECH is already in reasonable shape...Read More

SQL/SSRS/Data Repository tips, MEDITECH NPR Report Writer tips

Welcome to our Report Writing Blog! Welcome to the Iatric Systems Report Writing Blog. This blog will be the new home to our monthly SQL/SSRS/Data Repository Tips, MEDITECH NPR Report Writer Tips,...Read More

Save Trees and Charge for More ER Supplies

MEDITECH MAGIC or Client/Server – Force a report to PREVIEW and show bar codes for scanning A MEDITECH MAGIC customer recently asked us to produce a report that could show bar codes on the screen of...Read More

NPR Tip: "Echo Name" feature for Customer Report Screens (MAGIC Only)

You may have noticed that MEDITECH standard NPR report screens can have "display only" fields, but in customer reports, there is no attribute or standard method to provide the same feature. In a CDS,...Read More

DR+SQL Tip: Using UNION to Best Effect

MEDITECH migrations platform bring many challenges; one of them being that you may find yourself with a new LIVExDB in the picture and once you’re past the go-live you find that data is flowing only...Read More

DR+NPR Tip: Running Web-based Reports from a MEDITECH Menu

with Mitchell Lawrence, HIS Programmer Analyst at Iatric Systems Overview Many sites using SQL Server Reporting Services will use the External Links feature in MEDITECH to route a user to the "top"...Read More

SQL Tip - Managing Diagnosis Codes

(NOTE: This post only applies to hospitals operating MEDITECH 5.66 or lower. For hospitals operating MEDITECH 5.67 or higher, please refer to Thomas' March, 2016 post. Thank you.) A common challenge...Read More

NPR Tip:  What Happened on March 1st 1980?

MAGIC and Client/Server Time Stamps and how to use them. Example Reports Report with "elapsed time" information Export of Doctor Dictionary Entries created or edited on/after selected date. List of...Read More

SQL Tip —The Data Request Number

As we’ve discussed before in webinars, white papers and in our SQL training classes, we recommend that all DR-based reporting and extracting be driven by stored procedures. Those stored procedures be...Read More

NPR Tip: Every Module Can Have Room and Bed Index (MAGIC or Client/Server)

It is quite common to write reports for current inpatients, and a logical assumption by many NPR report writers is that such reports are best built in ADM, even when the data required is in some...Read More

NPR Tip: Keep Users Entertained During Download or Printing (MAGIC or Client/Server)

If you have a report that users are going to run and wait for, it can be nice to print some kind of progress message to the screen to keep them entertained. The easiest way to do this is to use the...Read More

SQL Tip -- Multi-value Parameters for SSRS

Continuing on from last month, when you start building DR + SSRS reports to replace or supplement NPR or RD reporting, you lose some conveniences from direct MEDITECH integration. One of them is the...Read More

SQL Tip — Facility Prompt by User Access

One of the challenges of moving from inside MEDITECH-integrated reporting (NPR or RD) outside into the realm of DR (SQL + SQL Server Reporting Services or Crystal Reports)- based reporting is that...Read More

Stop at Query if Patient is "on Coumadin" (MAGIC Only)

Someone from Mid Columbia Medical Center in Oregon asked for a way to stop at a query only if a patient was "on coumadin." I am going to interpret that to mean any active PHA order for Warfarin...Read More

SQL Tip -- Check Tables for Data

One of the challenges of the Data Repository (DR) is that we often need sets of tables populated with data and, despite everyone’s best efforts, when the rubber hits the road – one of them is empty....Read More

Getting Most Recent BAR Comment for Account (MAGIC, Client/Server, or 6.0)

A BAR analyst from a C/S hospital posted a request to the "Meditech-L" mailing list, asking for help in including the last billing comment and last billing comment date on a BAR.PAT report. MEDITECH...Read More

SQL Tip — Making Your Own Legos

Today we play with Lego™, or rather we make our own Lego. No, not this way, but in SQL. One of the tremendous things about SQL and the Data Repository is that you can roll your own building blocks...Read More

Writing files from NPR report to a shared folder from a scheduled or submerged report (MAGIC Only)

The only "officially supported" mechanism to write a file to a non-MEDITECH machine is to use FTP. This means you need to set up an FTP as a service on the destination machine, which typically...Read More

NPR Tip (MAGIC only) - Printing Radiology ($T RAD) Text and how to use ECB/ECE loop in a report

In October 2002, we published a tip on how to print Radiology report text in an NPR report. At the time, there was no field or utility to do this (or possibly I just liked to do things the hard way),...Read More

SQL Tip - Parsing Patient and Provider Names

Tis but patient name tis my enemy; Thou art five parts, though not in MEDITECH. What is MEDITECH? It is nor first, nor last, nor suffix, nor prefix, nor any other part belonging to a name. Oh! Be...Read More