TSQL One sequence of characters must follow another
First time question but thought I would see if anyone has a clear idea how to do this. Thank you.
I have a view that contains information about procedures in a hospital. Usual name and date fields and then there is a field that contains all the associated procedures that are concatenated into a single field and pipe delimited. Procedure codes are usually 4 digits and represent each individual procedure completed.
E.g. Name, DateOfBirth,|W822|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||
Procedure codes starting W833,W834,W835 should always be followed by a site code (where on the body the proc was carried out) Z00-Z93 or Z95-Z99
However, it may have a Y code in between (Y282,...) the W and the Z code
There may also be a Z94* following the Y* code
How could you construct a query to find incorrect procedures.
I assume the best way would be a rather convoluted CASE statement. Anyone have any other suggestions?
Thank you.
tsql case
asked Nov 28 '18 at 7:34
distressedinnhsdistressedinnhs
124
add a comment |
First time question but thought I would see if anyone has a clear idea how to do this. Thank you.
I have a view that contains information about procedures in a hospital. Usual name and date fields and then there is a field that contains all the associated procedures that are concatenated into a single field and pipe delimited. Procedure codes are usually 4 digits and represent each individual procedure completed.
E.g. Name, DateOfBirth,|W822|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||
Procedure codes starting W833,W834,W835 should always be followed by a site code (where on the body the proc was carried out) Z00-Z93 or Z95-Z99
However, it may have a Y code in between (Y282,...) the W and the Z code
There may also be a Z94* following the Y* code
How could you construct a query to find incorrect procedures.
I assume the best way would be a rather convoluted CASE statement. Anyone have any other suggestions?
Thank you.
tsql case
asked Nov 28 '18 at 7:34
distressedinnhsdistressedinnhs
124
Please try to set up a Minimal, Complete, and Verifiable example, at least provide a set of sample data with the expected output. Furthermore, it helps to show, what you've tried yourself.
– Shnugo
Nov 28 '18 at 8:02
Hi @distressednhs is there any chance of splitting that data structure in to 2 tables? One with the patient information and one with the procedure information? Then you can store each patient and procedure as a row rather than having concatenated columns which encourage complex case statements.
– Dwight Reynoldson
Nov 28 '18 at 8:06
Best option is not to use delimited strings in the first place....
– Zohar Peled
Nov 28 '18 at 10:01
add a comment |
First time question but thought I would see if anyone has a clear idea how to do this. Thank you.
I have a view that contains information about procedures in a hospital. Usual name and date fields and then there is a field that contains all the associated procedures that are concatenated into a single field and pipe delimited. Procedure codes are usually 4 digits and represent each individual procedure completed.
E.g. Name, DateOfBirth,|W822|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||
Procedure codes starting W833,W834,W835 should always be followed by a site code (where on the body the proc was carried out) Z00-Z93 or Z95-Z99
However, it may have a Y code in between (Y282,...) the W and the Z code
There may also be a Z94* following the Y* code
How could you construct a query to find incorrect procedures.
I assume the best way would be a rather convoluted CASE statement. Anyone have any other suggestions?
Thank you.
tsql case
asked Nov 28 '18 at 7:34
distressedinnhsdistressedinnhs
124
First time question but thought I would see if anyone has a clear idea how to do this. Thank you.
I have a view that contains information about procedures in a hospital. Usual name and date fields and then there is a field that contains all the associated procedures that are concatenated into a single field and pipe delimited. Procedure codes are usually 4 digits and represent each individual procedure completed.
E.g. Name, DateOfBirth,|W822|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||
Procedure codes starting W833,W834,W835 should always be followed by a site code (where on the body the proc was carried out) Z00-Z93 or Z95-Z99
However, it may have a Y code in between (Y282,...) the W and the Z code
There may also be a Z94* following the Y* code
How could you construct a query to find incorrect procedures.
I assume the best way would be a rather convoluted CASE statement. Anyone have any other suggestions?
Thank you.
tsql case
tsql case
asked Nov 28 '18 at 7:34
distressedinnhsdistressedinnhs
124
asked Nov 28 '18 at 7:34
distressedinnhsdistressedinnhs
124
asked Nov 28 '18 at 7:34
distressedinnhsdistressedinnhs
124
asked Nov 28 '18 at 7:34
distressedinnhsdistressedinnhs
124
asked Nov 28 '18 at 7:34
distressedinnhsdistressedinnhs
124
124
Please try to set up a Minimal, Complete, and Verifiable example, at least provide a set of sample data with the expected output. Furthermore, it helps to show, what you've tried yourself.
– Shnugo
Nov 28 '18 at 8:02
Hi @distressednhs is there any chance of splitting that data structure in to 2 tables? One with the patient information and one with the procedure information? Then you can store each patient and procedure as a row rather than having concatenated columns which encourage complex case statements.
– Dwight Reynoldson
Nov 28 '18 at 8:06
Best option is not to use delimited strings in the first place....
– Zohar Peled
Nov 28 '18 at 10:01
add a comment |
Please try to set up a Minimal, Complete, and Verifiable example, at least provide a set of sample data with the expected output. Furthermore, it helps to show, what you've tried yourself.
– Shnugo
Nov 28 '18 at 8:02
Hi @distressednhs is there any chance of splitting that data structure in to 2 tables? One with the patient information and one with the procedure information? Then you can store each patient and procedure as a row rather than having concatenated columns which encourage complex case statements.
– Dwight Reynoldson
Nov 28 '18 at 8:06
Best option is not to use delimited strings in the first place....
– Zohar Peled
Nov 28 '18 at 10:01
Please try to set up a Minimal, Complete, and Verifiable example, at least provide a set of sample data with the expected output. Furthermore, it helps to show, what you've tried yourself.
– Shnugo
Nov 28 '18 at 8:02
Please try to set up a Minimal, Complete, and Verifiable example, at least provide a set of sample data with the expected output. Furthermore, it helps to show, what you've tried yourself.
– Shnugo
Nov 28 '18 at 8:02
Hi @distressednhs is there any chance of splitting that data structure in to 2 tables? One with the patient information and one with the procedure information? Then you can store each patient and procedure as a row rather than having concatenated columns which encourage complex case statements.
– Dwight Reynoldson
Nov 28 '18 at 8:06
Hi @distressednhs is there any chance of splitting that data structure in to 2 tables? One with the patient information and one with the procedure information? Then you can store each patient and procedure as a row rather than having concatenated columns which encourage complex case statements.
– Dwight Reynoldson
Nov 28 '18 at 8:06
Best option is not to use delimited strings in the first place....
– Zohar Peled
Nov 28 '18 at 10:01
Best option is not to use delimited strings in the first place....
– Zohar Peled
Nov 28 '18 at 10:01
add a comment |
1 Answer
1
active
oldest
votes
If you just want to get rows where the procedure codes are in the wrong order you could use a function to validate your procedure codes:
CREATE FUNCTION fn_ValidateOrder ( @InputString NVARCHAR(MAX), @Delimiter NCHAR(1))
RETURNS BIT
AS
BEGIN
DECLARE @Valid BIT = 0, @Errors INT
/* Table to hold splitt string */
DECLARE @T TABLE (id INT IDENTITY(1,1),Val NVARCHAR(128) NULL)
INSERT INTO @T
SELECT value FROM STRING_SPLIT(@InputString,@Delimiter) WHERE value != ''
/* Validated order */
;WITH rn AS (
SELECT
ROW_NUMBER() OVER (ORDER BY val) AS rn
,id,
val
FROM @T
)
/* Count errors */
SELECT @Errors=SUM(CASE WHEN id IS NOT NULL THEN 1 ELSE 0 END) FROM rn WHERE id!=rn
/* Set return value */
IF @Errors>0
SET @Valid=0
ELSE
SET @Valid=1
RETURN @Valid
END
I used the following test data to validate:
/* Test Data*/
DECLARE @data TABLE (DateOfBirth VARCHAR(6),ProcedureCode VARCHAR(MAX))
INSERT INTO @data (DateOfBirth,ProcedureCode)
VALUES ('010176','|W822|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||'),
('120176','|W822|Z282|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||')
/* Validated test data */
SELECT
DateOfBirth,
dbo.fn_ValidateOrder(ProcedureCode,'|') AS ValidOrder,
ProcedureCode
FROM @data
WHERE dbo.fn_ValidateOrder(ProcedureCode,'|') = 0
Note that this will only work on SQL 2016 and newer, as the function use SPLIT_STRING.
answered Nov 28 '18 at 12:39
MariusEMariusE
463
Thank you MariusE. Will try to digest this when I get time and apply it. Looks very promising.
– distressedinnhs
Nov 28 '18 at 15:15
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53514319%2ftsql-one-sequence-of-characters-must-follow-another%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you just want to get rows where the procedure codes are in the wrong order you could use a function to validate your procedure codes:
CREATE FUNCTION fn_ValidateOrder ( @InputString NVARCHAR(MAX), @Delimiter NCHAR(1))
RETURNS BIT
AS
BEGIN
DECLARE @Valid BIT = 0, @Errors INT
/* Table to hold splitt string */
DECLARE @T TABLE (id INT IDENTITY(1,1),Val NVARCHAR(128) NULL)
INSERT INTO @T
SELECT value FROM STRING_SPLIT(@InputString,@Delimiter) WHERE value != ''
/* Validated order */
;WITH rn AS (
SELECT
ROW_NUMBER() OVER (ORDER BY val) AS rn
,id,
val
FROM @T
)
/* Count errors */
SELECT @Errors=SUM(CASE WHEN id IS NOT NULL THEN 1 ELSE 0 END) FROM rn WHERE id!=rn
/* Set return value */
IF @Errors>0
SET @Valid=0
ELSE
SET @Valid=1
RETURN @Valid
END
I used the following test data to validate:
/* Test Data*/
DECLARE @data TABLE (DateOfBirth VARCHAR(6),ProcedureCode VARCHAR(MAX))
INSERT INTO @data (DateOfBirth,ProcedureCode)
VALUES ('010176','|W822|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||'),
('120176','|W822|Z282|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||')
/* Validated test data */
SELECT
DateOfBirth,
dbo.fn_ValidateOrder(ProcedureCode,'|') AS ValidOrder,
ProcedureCode
FROM @data
WHERE dbo.fn_ValidateOrder(ProcedureCode,'|') = 0
Note that this will only work on SQL 2016 and newer, as the function use SPLIT_STRING.
answered Nov 28 '18 at 12:39
MariusEMariusE
463
Thank you MariusE. Will try to digest this when I get time and apply it. Looks very promising.
– distressedinnhs
Nov 28 '18 at 15:15
add a comment |
If you just want to get rows where the procedure codes are in the wrong order you could use a function to validate your procedure codes:
CREATE FUNCTION fn_ValidateOrder ( @InputString NVARCHAR(MAX), @Delimiter NCHAR(1))
RETURNS BIT
AS
BEGIN
DECLARE @Valid BIT = 0, @Errors INT
/* Table to hold splitt string */
DECLARE @T TABLE (id INT IDENTITY(1,1),Val NVARCHAR(128) NULL)
INSERT INTO @T
SELECT value FROM STRING_SPLIT(@InputString,@Delimiter) WHERE value != ''
/* Validated order */
;WITH rn AS (
SELECT
ROW_NUMBER() OVER (ORDER BY val) AS rn
,id,
val
FROM @T
)
/* Count errors */
SELECT @Errors=SUM(CASE WHEN id IS NOT NULL THEN 1 ELSE 0 END) FROM rn WHERE id!=rn
/* Set return value */
IF @Errors>0
SET @Valid=0
ELSE
SET @Valid=1
RETURN @Valid
END
I used the following test data to validate:
/* Test Data*/
DECLARE @data TABLE (DateOfBirth VARCHAR(6),ProcedureCode VARCHAR(MAX))
INSERT INTO @data (DateOfBirth,ProcedureCode)
VALUES ('010176','|W822|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||'),
('120176','|W822|Z282|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||')
/* Validated test data */
SELECT
DateOfBirth,
dbo.fn_ValidateOrder(ProcedureCode,'|') AS ValidOrder,
ProcedureCode
FROM @data
WHERE dbo.fn_ValidateOrder(ProcedureCode,'|') = 0
Note that this will only work on SQL 2016 and newer, as the function use SPLIT_STRING.
answered Nov 28 '18 at 12:39
MariusEMariusE
463
Thank you MariusE. Will try to digest this when I get time and apply it. Looks very promising.
– distressedinnhs
Nov 28 '18 at 15:15
add a comment |
If you just want to get rows where the procedure codes are in the wrong order you could use a function to validate your procedure codes:
CREATE FUNCTION fn_ValidateOrder ( @InputString NVARCHAR(MAX), @Delimiter NCHAR(1))
RETURNS BIT
AS
BEGIN
DECLARE @Valid BIT = 0, @Errors INT
/* Table to hold splitt string */
DECLARE @T TABLE (id INT IDENTITY(1,1),Val NVARCHAR(128) NULL)
INSERT INTO @T
SELECT value FROM STRING_SPLIT(@InputString,@Delimiter) WHERE value != ''
/* Validated order */
;WITH rn AS (
SELECT
ROW_NUMBER() OVER (ORDER BY val) AS rn
,id,
val
FROM @T
)
/* Count errors */
SELECT @Errors=SUM(CASE WHEN id IS NOT NULL THEN 1 ELSE 0 END) FROM rn WHERE id!=rn
/* Set return value */
IF @Errors>0
SET @Valid=0
ELSE
SET @Valid=1
RETURN @Valid
END
I used the following test data to validate:
/* Test Data*/
DECLARE @data TABLE (DateOfBirth VARCHAR(6),ProcedureCode VARCHAR(MAX))
INSERT INTO @data (DateOfBirth,ProcedureCode)
VALUES ('010176','|W822|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||'),
('120176','|W822|Z282|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||')
/* Validated test data */
SELECT
DateOfBirth,
dbo.fn_ValidateOrder(ProcedureCode,'|') AS ValidOrder,
ProcedureCode
FROM @data
WHERE dbo.fn_ValidateOrder(ProcedureCode,'|') = 0
Note that this will only work on SQL 2016 and newer, as the function use SPLIT_STRING.
answered Nov 28 '18 at 12:39
MariusEMariusE
463
If you just want to get rows where the procedure codes are in the wrong order you could use a function to validate your procedure codes:
CREATE FUNCTION fn_ValidateOrder ( @InputString NVARCHAR(MAX), @Delimiter NCHAR(1))
RETURNS BIT
AS
BEGIN
DECLARE @Valid BIT = 0, @Errors INT
/* Table to hold splitt string */
DECLARE @T TABLE (id INT IDENTITY(1,1),Val NVARCHAR(128) NULL)
INSERT INTO @T
SELECT value FROM STRING_SPLIT(@InputString,@Delimiter) WHERE value != ''
/* Validated order */
;WITH rn AS (
SELECT
ROW_NUMBER() OVER (ORDER BY val) AS rn
,id,
val
FROM @T
)
/* Count errors */
SELECT @Errors=SUM(CASE WHEN id IS NOT NULL THEN 1 ELSE 0 END) FROM rn WHERE id!=rn
/* Set return value */
IF @Errors>0
SET @Valid=0
ELSE
SET @Valid=1
RETURN @Valid
END
I used the following test data to validate:
/* Test Data*/
DECLARE @data TABLE (DateOfBirth VARCHAR(6),ProcedureCode VARCHAR(MAX))
INSERT INTO @data (DateOfBirth,ProcedureCode)
VALUES ('010176','|W822|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||'),
('120176','|W822|Z282|W833|Z846|Z942|||||||||||||||||||||||||||||||||||||')
/* Validated test data */
SELECT
DateOfBirth,
dbo.fn_ValidateOrder(ProcedureCode,'|') AS ValidOrder,
ProcedureCode
FROM @data
WHERE dbo.fn_ValidateOrder(ProcedureCode,'|') = 0
Note that this will only work on SQL 2016 and newer, as the function use SPLIT_STRING.
answered Nov 28 '18 at 12:39
MariusEMariusE
463
answered Nov 28 '18 at 12:39
MariusEMariusE
463
answered Nov 28 '18 at 12:39
MariusEMariusE
463
answered Nov 28 '18 at 12:39
MariusEMariusE
463
463
Thank you MariusE. Will try to digest this when I get time and apply it. Looks very promising.
– distressedinnhs
Nov 28 '18 at 15:15
add a comment |
Thank you MariusE. Will try to digest this when I get time and apply it. Looks very promising.
– distressedinnhs
Nov 28 '18 at 15:15
Thank you MariusE. Will try to digest this when I get time and apply it. Looks very promising.
– distressedinnhs
Nov 28 '18 at 15:15
Thank you MariusE. Will try to digest this when I get time and apply it. Looks very promising.
– distressedinnhs
Nov 28 '18 at 15:15
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53514319%2ftsql-one-sequence-of-characters-must-follow-another%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please try to set up a Minimal, Complete, and Verifiable example, at least provide a set of sample data with the expected output. Furthermore, it helps to show, what you've tried yourself.
– Shnugo
Nov 28 '18 at 8:02
Hi @distressednhs is there any chance of splitting that data structure in to 2 tables? One with the patient information and one with the procedure information? Then you can store each patient and procedure as a row rather than having concatenated columns which encourage complex case statements.
– Dwight Reynoldson
Nov 28 '18 at 8:06
Best option is not to use delimited strings in the first place....
– Zohar Peled
Nov 28 '18 at 10:01