August 15, 2013 at 10:31 am
I have the values in a column like
'email=abc@microsoft.com;rota=mon-fri'
And i need in my select statements to pickup email value and rota value.
Can anybody help please? or can anybody come up with a better solution to store the values in a column to easily retrieve those two values.
Thank you.
August 15, 2013 at 10:52 am
With this code, you should be able to store these values in separate columns. Although, it expects to only have email and rota in the values. If something can be different, or the values can be in different order, more rules should be applied.
SELECT SUBSTRING( string, 7, CHARINDEX( ';', string) - 7) email,
SUBSTRING( string, CHARINDEX( ';', string) + 6, 8000) rota
FROM ( VALUES(
'email=abc@microsoft.com;rota=mon-fri')) Data(string)
August 16, 2013 at 3:18 am
To make Luis' code a little more generic, you could code it as follows:
SELECT
SUBSTRING(String,CHARINDEX('email=',String)+6
,CASE WHEN CHARINDEX(';',String,CHARINDEX('email=',String)+6) = 0
THEN LEN(string)
ELSE CHARINDEX(';',String,CHARINDEX('email=',String)+6)-(CHARINDEX('email=',String)+6) END) AS [Email]
,SUBSTRING(String,CHARINDEX('rota=',String)+5
,CASE WHEN CHARINDEX(';',String,CHARINDEX('rota=',String)+5) = 0
THEN LEN(string)
ELSE CHARINDEX(';',String,CHARINDEX('rota=',String)+5)-(CHARINDEX('rota=',String)+5) END) AS [Rota]
,SUBSTRING(String,CHARINDEX('newdata=',String)+8
,CASE WHEN CHARINDEX(';',String,CHARINDEX('newdata=',String)+8) = 0
THEN LEN(string)
ELSE CHARINDEX(';',String,CHARINDEX('newdata=',String)+8)-(CHARINDEX('newdata=',String)+8) END) AS [NewData]
FROM ( VALUES(
'rota=mon-fri;email=abc@microsoft.com;newdata=stuff')) Data(string)
This allows you to have the parameters in any order in the string as demonstrated. Ugly though.
The nicest way to do this is probably to write a function to do the string functions so that your SQL call is nice and clean and you can just pass in the string and any key value without having to worry about how long it is. The function could be something like:
CREATE FUNCTION [dbo].[GetKeyValue]
(
@String AS varchar(100)
,@Key AS varchar(50)
)
RETURNS varchar(50)
BEGIN
DECLARE @data varchar(50)
DECLARE @Keylen smallint
SELECT @Keylen = LEN(@Key)+1
SELECT @data =
SUBSTRING(@String,CHARINDEX(@Key,@String)+@KeyLen,
CASE WHEN CHARINDEX(';',@String,CHARINDEX(@Key,@String)+@KeyLen) = 0
THEN LEN(@String)
ELSE CHARINDEX(';',@String,CHARINDEX(@Key,@String)+@KeyLen)-(CHARINDEX(@Key,@String)+@KeyLen) END)
RETURN @data
END
GO
Your SQL statement would then be similar to the following:
SELECT dbo.GetKeyValue(string,'email') AS [Email]
,dbo.GetKeyValue(string,'rota') AS [Rota]
,dbo.GetKeyValue(string,'newdata') AS [New Data]
FROM ( VALUES(
'email=abc@microsoft.com;rota=mon-fri;newdata=stuff')) Data(string)
August 16, 2013 at 3:23 am
I'd use a CROSS APPLY cascade. This article [/url]has at least one example which is very similar to your requirements.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 16, 2013 at 4:53 am
Using a function for this calculation seems like an excellent idea.
I rewrote Christy's code as an inline table-valued function for speed. I also replaced some repeated expressions with a couple of cte:s
Like this:
CREATE FUNCTION [dbo].[GetKeyValue](@String AS varchar(100), @key AS varchar(50))
RETURNS table
as
return
with
cte1 as (select KeyLen = len(@Key), p1 = charindex(@Key, @String)),
cte2 as (select p2 = charindex(';', @String, p1+KeyLen), * from cte1)
select
Result = case
when p1=0 then null
when p2=0 then substring(@String, p1+KeyLen, 8000)
else substring(@String, p1+KeyLen, p2-(p1+KeyLen))
end
from cte2
go
SELECT string, email = x1.result, rota = x2.result, newdata = x3.result
FROM ( VALUES(
'email=abc@microsoft.com;rota=mon-fri;newdatax=stuff')) Data(string)
cross apply dbo.GetKeyValue(string, 'email=') x1
cross apply dbo.GetKeyValue(string, 'rota=') x2
cross apply dbo.GetKeyValue(string, 'newdata=') x3
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply