[MT-L] SQL Number manipulation
Laura Wilson
Does anyone have SQL code that accepts a patacct number without the zeros (as Meditech does) and reformats the it to have the appropriate number of zeros as needed for SQL searches?
Ex. If user inputs X12345 then it would be reformatted to X00000012345
Also, we have some numbers that have one alpha character as the leading character and some that have two alpha. Ex. X00000012345 or XY0000012345
Thank you in advance if you are willing to share!
Laura
P.S. Sorry if this is a duplicate! I believe the first one got caught and encrypted as possible PHI.
Laura Wilson
Director, Enterprise Applications and Reporting Services
TANNER HEALTH SYSTEM
INFORMATION TECHNOLOGY
O 770.836.9791 | lwi...@tanner.org | www.tanner.org
This e-mail and any files or attachments transmitted with it contain information that is confidential and privileged. This information is intended only for the use of the individual(s) and entit(ies) to whom it is addressed. If you are not the intended recipient, any disclosure, copying, printing or use of this information is strictly prohibited and possibly a violation of federal or state law and regulations. If you have received this information in error, please contact the sender immediately and destroy all copies of this communication.
Sexton Kevin - Fauquier
Laura,
This is a pretty simple way, but I’m interested to see what other people might post (REPLICATE,STUFF,etc.)
--IF the 2nd digit is a number insert 6 zero's, otherwise insert 5. Replace the Xstring with your column name.
SELECT Vnumber =
CASE ISNUMERIC(SUBSTRING('XY67890',2,1)) WHEN 1 then LEFT('X12345',1)+'000000'+RIGHT('X12345',5) else
LEFT('XY67890',2)+'00000'+RIGHT('X12345',5) END
--FROM MyTable
-Kevin
From: Meditech-l [mailto:meditech-...@mtusers.com]
On Behalf Of Laura Wilson
Sent: Friday, June 17, 2016 9:39 AM
To: medit...@mtusers.com
Subject: [EXTERNAL] [MT-L] SQL Number manipulation
WARNING: This email originated outside LifePoint Health’s
email system.
DO NOT CLICK links or attachments unless you recognize the sender and know the content is safe.
The information transmitted via this e-mail is intended only for the person or entity to which it is addressed and may contain confidential and/or proprietary information. Any use, review, retransmission, dissemination or other use of, or pursuing of any action in reliance upon this information by persons or entities other than the intended recipient is strictly prohibited. If you are the recipient of this e-mail transmission in error, please reply to the sender and delete the material from any computer. Thank you.
Lykins, Patrick
Hello Laura,
This is similar to what Kevin did. We have the same issue with visits from our home health agency. I didn’t have the alpha coded in because it wasn’t an issue but just retrofitted it.
/*Add missing zero’s into visit number*/
DECLARE @sVisit varchar(12)
set @sVisit = 'V103448'
SELECT * FROM [Livendb].[dbo].[AbstractData]
where AccountNumber = iif(Isnumeric(SUBSTRING(@sVisit,2,1))=1,Left(@sVisit,1)+right('00000000000'+substring(@sVisit,2,11),11),Left(@sVisit,2)+right('0000000000'+substring(@sVisit,3,10),10))
/*I check to see if the second character is numeric, if so then I add 11 0’s to the front and then take the right 11 characters. If it was alpha then I take the first two characters from the visit number and then add 10 zeros and strip off the first 10 characters*/
Patrick L. Lykins
Senior Programmer Analyst
Fairmont Regional Medical Center
1325 Locust Ave.
Fairmont, WV 26554
Garry McAninch
create FUNCTION [dbo].udf_ActNo_String
-- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- UDF Function Name [udf_ActNo_String]
--
-- Application Various
-- Description For Formatting Account Numbers to full size with leading zeros
--
-- Created By: Garry McAninch, Dimensions Analysis ( gmca...@dimensionsanalysis.com )
-- Creation Date: June 21, 2016
-- Revision Date: June 21, 2016
--
-- For the Exclusive use by Dimensions Analysis Customers
-- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(
@ActNo VARCHAR(30)
)
RETURNS VARCHAR(30)
AS
BEGIN
RETURN
CASE
WHEN @ActNo IS NULL — Blank # ?
THEN NULL
WHEN LEN(@ActNo) < 3 — Invalid # ?
THEN @ActNo
ELSE
CASE
ISNUMERIC(SUBSTRING(@ActNo,2,1))
WHEN 1
THEN LEFT(@ActNo,1) + RIGHT('0000000000000'+ @ActNo,11) -- 2 char a/c
ELSE LEFT(@ActNo,2) + RIGHT('0000000000000'+ @ActNo,10) -- 1 char a/c
END
END
END
DECLARE @Account VARCHAR(30) = 'X12345'; -- to 'X00000012345'
SELECT
@Account AS 'EnteredAccountNumber',
dbo.udf_ActNo_String(@Account) AS 'FormattedAccountNumber'
FROM Livendb.dbo.BarVisits VISID (NOLOCK)
WHERE VISIT.AccountNumber = dbo.udf_ActNo_String(@Account)
Garry McAninch
Principal
Dimension Analysis
Phone : 905-704-1356
Mobile : 905-941-1356
Fax : 905-688-2256
Email : * gmca...@dimensionsanalysis.com
Web : www.dimensionsanalysis.com
Sent from the: MAC 15 Retina
Data Warehouse Engineering * Digital Dashboards * Multi-Dimensional Reporting * Information Management Consulting * Data Integration
P Before printing, please consider the environment.
This communication is intended solely for the addressee(s) and contains information that is privileged, confidential and subject to copyright. Any unauthorized use, copying, review or disclosure is prohibited. If received in error, please notify us immediately by return-email.
Date: Friday, June 17, 2016 at 9:39 AM
To: "medit...@mtusers.com" <medit...@mtusers.com>
Subject: [MT-L] SQL Number manipulation
Does anyone have SQL code that accepts a patacct number without the zeros (as Meditech does) and reformats the it to have the appropriate number of zeros as needed for SQL searches?
Ex. If user inputs X12345 then it would be reformatted to X00000012345
Also, we have some numbers that have one alpha character as the leading character and some that have two alpha. Ex. X00000012345 or XY0000012345
Thank you in advance if you are willing to share!
Laura
P.S. Sorry if this is a duplicate! I believe the first one got caught and encrypted as possible PHI.
Laura Wilson
Director, Enterprise Applications and Reporting Services
TANNER HEALTH SYSTEM
INFORMATION TECHNOLOGY
O 770.836.9791 | lwi...@tanner.org | www.tanner.org
This e-mail and any files or attachments transmitted with it contain information that is confidential and privileged. This information is intended only for the use of the individual(s) and entit(ies) to whom it is addressed. If you are not the intended recipient, any disclosure, copying, printing or use of this information is strictly prohibited and possibly a violation of federal or state law and regulations. If you have received this information in error, please contact the sender immediately and destroy all copies of this communication.