Help Required!!

• faisal_xyz

  SSC Enthusiast

  Points: 164

  More actions

  August 15, 2013 at 10:31 am

  #278781

  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.

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  August 15, 2013 at 10:52 am

  #1641871

  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)

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Chris M

  SSCommitted

  Points: 1815

  More actions

  August 16, 2013 at 3:18 am

  #1642037

  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)

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 16, 2013 at 3:23 am

  #1642039

  I'd use a CROSS APPLY cascade. This article [/url]has at least one example which is very similar to your requirements.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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

• Stefan_G

  SSCertifiable

  Points: 6609

  More actions

  August 16, 2013 at 4:53 am

  #1642080

  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