Parse long string list value 1

[hansolo]

Coldfusion uses database to store client variable value but the variables are stored in a field with dynamic list of string such as below:

record 1:

Column1 name: DATA
Column1 value :#timeout=30#name=John#Status=married#

Column2 name: LVISIT
Column2 value: 4/11/2003 12:48:27 PM

record 2:

Column name: DATA
Column value:
#AccountStatus=1#timeout=60#name=David#Status=married#Address=James Street#

Column2 name: LVISIT
Column2 value: 4/11/2003 11:48:27 PM

Appreciate if someone could quide me on how to use query string function in Microsoft SQL 7 with records which have timeout. I can't find a way to extract the timeout value in DATA column and compare it with the different of the last visit date(LVISIT) and current time. I can parse the value using Coldfusion script after Query but prefers if this can be done everything in SQL.

Thanks in advance.

 

[rt63]

Try this


select convert(int,substring(data , charindex('#timeout=',data) + 9 ,
charindex('#',data,charindex('#timeout=',data)+1)
- charindex('#timeout=',data) - 9)) as timeout
, datediff(ss,lvisit,getdate()) as time_since_last_visit
from #temp
where charindex('#timeout=',data) > 0


This will give you the value for timeout parameter in the data column and the time difference (in seconds) between current time and last visit for the respective record.
RT

 

[hansolo]

It works great. Thanks a million