Return values to left AND right of delimiter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return values to left AND right of delimiter

Author

Topic

ImTheodore
Starting Member

16 Posts

Posted - 2011-12-16 : 20:33:41

I have a large text field I would like to pull out the time from. The field contains a long written response and makes use of a time in the text. The time in in the format 00:00. I need to pull the time out of the text.

I am able to use substring to pull the :00, but I cannot figure out how to get the first part of the time 00.

This is what I am currently using to get the minutes:

SUBSTRING([Field1],PATINDEX('%:%', [Field1]),-3)

The result is :00, I still need the hours so I can have 00:00

Please note, the field I am searching for is not a true timestamp, it is a time typed in by the user within within a note in a large text field.

For example the field may look like this:

Taken to floor, medicated patient 10:15 discharged patient etc...

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 01:41:29

for that, if you're sure that no other numeric data comes in field, you can use like

SUBSTRING(field,PATINDEX('%[0-9]%',field),5)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ImTheodore
Starting Member

16 Posts

Posted - 2011-12-19 : 07:00:00

Numeric data almost always comes before the time.

Any other ideas?

quote:
Originally posted by visakh16

for that, if you're sure that no other numeric data comes in field, you can use like

SUBSTRING(field,PATINDEX('%[0-9]%',field),5)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-19 : 08:29:33

You can make it more and more selective if you add more conditions. For example, enhancing Visakh's query a little bit, you could do the following:


LTRIM(RTRIM(SUBSTRING(field,PATINDEX('%[0-9]:[0-9]%',field)-1,5)))

That assumes that there is numeric data before and after the colon.

ImTheodore
Starting Member

16 Posts

Posted - 2011-12-19 : 10:06:18

Thats perfect, thanks to both of you!

quote:
Originally posted by sunitabeck

You can make it more and more selective if you add more conditions. For example, enhancing Visakh's query a little bit, you could do the following:

LTRIM(RTRIM(SUBSTRING(field,PATINDEX('%[0-9]:[0-9]%',field)-1,5)))
That assumes that there is numeric data before and after the colon.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 10:39:09

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ImTheodore
Starting Member

16 Posts

Posted - 2011-12-27 : 10:39:57

Ok, I was able to use a version of the below code, however, now I noticed that there may be more than one instance of the time in the field. Is there a way to find the 2nd, 3rd, 4th instance?

Thanks

quote:
Originally posted by sunitabeck

You can make it more and more selective if you add more conditions. For example, enhancing Visakh's query a little bit, you could do the following:

LTRIM(RTRIM(SUBSTRING(field,PATINDEX('%[0-9]:[0-9]%',field)-1,5)))
That assumes that there is numeric data before and after the colon.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-27 : 10:54:42

T-SQL has limited string manipulation capabilities when compared to something like .Net, which has a regular expression class. So your options are:

a) Do it the hard way - manually repeat the process to find however many time stamps you want to find.

b) Use a CLR stored procedure. CLR stored procs can make use of .Net facilities, so you would be able to do a regex match.

c) Something like what I am showing below. Before I post the code, I want to disown it because I am not too pleased with it to begin with

DECLARE @x VARCHAR(255);
SET @x = 'abcd 11:30, but then 12:30 and so 1:35 is the last, but could be 14:44 or 15:47';

;WITH cte AS
(
	SELECT 
		LTRIM(RTRIM(SUBSTRING(@x,PATINDEX('%[0-9]:[0-9]%',@x)-1,5))) TimeValue,
		STUFF(@x,1,PATINDEX('%[0-9]:[0-9]%',@x)+1,'') strRemain
	UNION ALL
	SELECT
		LTRIM(RTRIM(SUBSTRING(strRemain,PATINDEX('%[0-9]:[0-9]%',strRemain)-1,5))) TimeValue,
		STUFF(strRemain,1,PATINDEX('%[0-9]:[0-9]%',strRemain)+1,'') strRemain
	FROM
		cte
	WHERE
		PATINDEX('%[0-9]:[0-9]%',strRemain) > 0
)
SELECT * FROM cte;

ImTheodore
Starting Member

16 Posts

Posted - 2011-12-27 : 12:12:21

I should add, I really only need the last occurance it that is easier

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-27 : 12:23:55

Ah! If you need only the last occurrence, that is an easy jump from the previous code that I had posted on 12/19/2011.

REVERSE(LTRIM(RTRIM(SUBSTRING(REVERSE(field),PATINDEX('%[0-9]:[0-9]%',REVERSE(field))-1,5))))

ImTheodore
Starting Member

16 Posts

Posted - 2011-12-27 : 14:05:14

Wow, I sure hope its that easy, maybe I'm missing something simple:

REVERSE(LTRIM(RTRIM(SUBSTRING(REVERSE(LABS),PATINDEX('%) Final results **Test**%',REVERSE(LABS))-5,5)))) as [Lab Results Timestamp Last]

Msg 8116, Level 16, State 2, Line 1
Argument data type text is invalid for argument 1 of reverse function.
Msg 256, Level 16, State 1, Line 1
The data type void type is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

quote:
Originally posted by sunitabeck

Ah! If you need only the last occurrence, that is an easy jump from the previous code that I had posted on 12/19/2011.
REVERSE(LTRIM(RTRIM(SUBSTRING(REVERSE(field),PATINDEX('%[0-9]:[0-9]%',REVERSE(field))-1,5))))

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-27 : 14:46:16

The error message suggests that the column "LABS" is of data type text. You can cast it to varchar(max) which would fix that problem; i.e., replace each occurrence of LABS with CAST(LABS AS VARCHAR(MAX)).

But, the premise on which the query is written is that there are occurrences of a digit followed by a colon followed by another digit. Given that, I did not follow the presence of the string '%) Final results **Test**%' in the query you posted.

ImTheodore
Starting Member

16 Posts

Posted - 2011-12-27 : 15:17:05

Sorry to complicate it for you...
When I looked through the fields, I noticed several "times" listed in the field. I ended up searching for the text that always followed the "time", to get the "time". Now of course, the string is in the field more than once.

quote:
Originally posted by sunitabeck

The error message suggests that the column "LABS" is of data type text. You can cast it to varchar(max) which would fix that problem; i.e., replace each occurrence of LABS with CAST(LABS AS VARCHAR(MAX)).

But, the premise on which the query is written is that there are occurrences of a digit followed by a colon followed by another digit. Given that, I did not follow the presence of the string '%) Final results **Test**%' in the query you posted.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-27 : 20:06:14

I am afraid I still didn't understand your explanation. The way you have written it, the query would run, but it would be searching for the string ") Final results **Test**" rather than a time such as 11:25

ImTheodore
Starting Member

16 Posts

Posted - 2011-12-28 : 07:29:57

Ok, hopefully this will help. Below is a sample of a field where I must search to find the last Test Result time. I do not care about each test time anymore, I only care about when they are all completed. This field may contain only one test or many as you see below, and yes, this is an actual field, and it's ugly:

Laboratory Tests: URINALYSIS, POC: (COLL: 06/31/2011 21:40) ( MsgRcvd 06/31/2011 21:41) Final results **Test** **Result** **Flag** **Units** **(Reference)** GLUCOSE Negative mg/dL (NEG) KETONE 15 mg/dL (NEG) SPECIFIC GRAVITY, UR 1.210 (1.003-1.030) pH, URINE 7.2 (4.5-8.0) PROTEIN Negative mg/dL (NEG) NITRITE Negative (NEG) BLOOD, POC Negative (NEG) LEUKOCYTES, POC Small (NEG) CT CERVICAL SPINE WITHOUT CONTRAST: ( MsgRcvd 08/30/2010 21:33) Final results (COLL: 06/31/2011 19:53) ( MsgRcvd 06/31/2011 19:55) New Order TROPONIN 2 reflex to CKIMB if result * 0.04: (COLL: 06/31/2011 19:53) ( MsgRcvd 06/31/2011 19:55) New Order TROPONIN 1 reflex to CKIMB if result * 0.04: (COLL: 06/31/2011 21:05) ( MsgRcvd 06/31/2011 21:06) Final results **Test** **Result** **Flag** **Units** **(Reference)** TROPONIN I <0.04 ng/mL (<0.04) COMP METAB PANEL: (COLL: 06/31/2011 21:35) ( MsgRcvd 06/31/2011 21:36) Final results **Test** **Result** **Flag** **Units** **(Reference)** GLUCOSE 98 mg/dL (65-99) BUN 27 mg/dL (10-26) CREATININE 1.1 mg/dL (0.7-1.5) SODIUM 135 mEq/L (135-146) POTASSIUM 3.1 mEq/L (3.5-5.2) CHLORIDE 101 mEq/L (96-109) CARBON DIOXIDE 21 mEq/L (22-33) CALCIUM 9.8 mg/dL (8.2-10.4) ALKALINE PHOSPHATASE 120 U/L (30-136) ALBUMIN 4.9 g/dL (3.5-5.0) BILIRUBIN,TOTAL 1.2 mg/dL (0.2-1.3)

quote:
Originally posted by sunitabeck

I am afraid I still didn't understand your explanation. The way you have written it, the query would run, but it would be searching for the string ") Final results **Test**" rather than a time such as 11:25

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-28 : 07:46:35

When I run the recursive query I had posted on 12/27/2011 : 10:54:42, it gives me the following:

TimeValue	strRemain...
21:40       40) ( MsgRcvd 06/31/2011 21:41) Final...
21:41       41) Final results **Test** **Result**...
21:33       33) Final results (COLL: 06/31/2011 1...
19:53       53) ( MsgRcvd 06/31/2011 19:55) New O...
19:55       55) New Order TROPONIN 2 reflex to CK...
19:53       53) ( MsgRcvd 06/31/2011 19:55) New O...
19:55       55) New Order TROPONIN 1 reflex to CK...
21:05       05) ( MsgRcvd 06/31/2011 21:06) Final...
21:06       06) Final results **Test** **Result**...
21:35       35) ( MsgRcvd 06/31/2011 21:36) Final...
21:36       36) Final results **Test** **Result**

So, the last time stamp in the string is 21:36. If you run the query that I had posted at 12/27/2011 : 12:23:55, you do get that result as in

declare @field varchar(max);

set @field = 'Laboratory Tests: URINALYSIS, POC: (COLL: 06/31/2011 21:40) ( MsgRcvd 06/31/2011 21:41) 
	Final results **Test** **Result** **Flag** **Units** **(Reference)** GLUCOSE Negative mg/dL (NEG) 
	KETONE 15 mg/dL (NEG) SPECIFIC GRAVITY, UR 1.210 (1.003-1.030) pH, URINE 7.2 (4.5-8.0) PROTEIN Negative 
	mg/dL (NEG) NITRITE Negative (NEG) BLOOD, POC Negative (NEG) LEUKOCYTES, POC Small (NEG) CT CERVICAL 
	SPINE WITHOUT CONTRAST: ( MsgRcvd 08/30/2010 21:33) Final results (COLL: 06/31/2011 19:53) ( MsgRcvd 
	06/31/2011 19:55) New Order TROPONIN 2 reflex to CKIMB if result * 0.04: (COLL: 06/31/2011 19:53) ( 
		MsgRcvd 06/31/2011 19:55) New Order TROPONIN 1 reflex to CKIMB if result * 0.04: (COLL: 
		06/31/2011 21:05) ( MsgRcvd 06/31/2011 21:06) Final results **Test** **Result** **Flag** **Units** 
		**(Reference)** TROPONIN I <0.04 ng/mL (<0.04) COMP METAB PANEL: (COLL: 06/31/2011 21:35) ( 
		**MsgRcvd 06/31/2011 21:36) Final results **Test** **Result** **Flag** **Units** **(Reference)** 
		**GLUCOSE 98 mg/dL (65-99) BUN 27 mg/dL (10-26) CREATININE 1.1 mg/dL (0.7-1.5) SODIUM 135 mEq/L (135-146) 
		**POTASSIUM 3.1 mEq/L (3.5-5.2) CHLORIDE 101 mEq/L (96-109) CARBON DIOXIDE 21 mEq/L (22-33) 
		**CALCIUM 9.8 mg/dL (8.2-10.4) ALKALINE PHOSPHATASE 120 U/L (30-136) ALBUMIN 4.9 g/dL (3.5-5.0) 
		**BILIRUBIN,TOTAL 1.2 mg/dL (0.2-1.3) ';
SELECT REVERSE(LTRIM(RTRIM(SUBSTRING(REVERSE(@field),PATINDEX('%[0-9]:[0-9]%',REVERSE(@field))-1,5))))

Is that what you are looking for, or are you looking for the 21:41 that is in the string as well - which is a later time than 21:36, but is not the last time stamp in the string.

ImTheodore
Starting Member

16 Posts

Posted - 2011-12-28 : 07:52:29

I was assuming that the timestamps were in order, as I/you see it now, they may not be. I really want the latest time, but if that is not obtainable I'll keep assuming that the last time is the last time and run with that.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-28 : 08:03:58

If you just want the last occurence of the timestamp, the query with the REVERSE that I posted a few minutes ago would do that. If you do want to really get the latest timestamp, you can use the following. It is doing more work, so if you have hundreds of thousands of rows in your table, it may take a long time.

--- TEST DATA
CREATE TABLE #tmp(TestId INT, field VARCHAR(MAX));
INSERT INTO #tmp VALUES 
	(1,'Time1: 11:30 Time2: 12:25 Time3: 11:10'),
	(2,'Time1: 8:30 Time2: 9:25 Time3: 11:30'),
	(3,'Time1: 13:30 Time2: 11:25 Time3: 9:30');

-- QUERY
;WITH cte AS
(
	SELECT 
		TestId,
		LTRIM(RTRIM(SUBSTRING(field,PATINDEX('%[0-9]:[0-9]%',field)-1,5))) TimeValue,
		STUFF(field,1,PATINDEX('%[0-9]:[0-9]%',field)+1,'') strRemain
	FROM
		#tmp
	UNION ALL
	SELECT
		TestId,
		LTRIM(RTRIM(SUBSTRING(strRemain,PATINDEX('%[0-9]:[0-9]%',strRemain)-1,5))) TimeValue,
		STUFF(strRemain,1,PATINDEX('%[0-9]:[0-9]%',strRemain)+1,'') strRemain
	FROM
		cte
	WHERE
		PATINDEX('%[0-9]:[0-9]%',strRemain) > 0
)
SELECT TestId, MAX(CAST(TimeValue AS TIME)) FROM cte GROUP BY TestId ;

-- CLEANUP
DROP TABLE #tmp;

ImTheodore
Starting Member

16 Posts

Posted - 2011-12-28 : 08:21:26

There are over 300,000 rows so I'm not sure which solution I will use. I haven't gotten either to work yet, I just found out that the DB I am connected to is Pervasive, so I have to change up the convert function a little bit.
Up until that point everything worked without change. I really appreciate you taking your time to help. I'll have to follow more of your posts to learn more...In the past I always built VB apps to get such difficult data, I am finding out that SQL is more powerful than I thought.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-28 : 09:19:17

If you have a UAT environment, you could try to run the query there to evaluate the performance. Or you could do it in chunks, if it is a one-time thing.

As for VB/C# vs SQL: there are so many things that SQL is exceptionally good at that would be very hard to do in C#/VB. I used to be C# programmer (and still claim that I am one when no real C# programmers are listening

) but there is a beauty and elegance about databases and SQL that I love.

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-28 : 11:06:59

IF you have a UAT environment.....NOBODY knows what that is

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources