Create Function

• yogi123

  SSCrazy

  Points: 2932

  More actions

  October 10, 2013 at 2:51 pm

  #299632

  Hello

  I need to Create a Function who pulls data from string and load to the table. here is sample string

  'CHG_FEP_SVC_GRP_ID(1) :: Expected Value = 1 :: Actual Value = 20'

  now I need to load to Expected Value in Amt1 column and Actual Value in Amt2 Column which is as in below table

  ErrorAmt1Amt2

  CHG_FEP_SVC_GRP_ID(1) :: Expected Value = 1 :: Actual Value = 20120

  Please help me to that

  Thanks

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  October 10, 2013 at 3:16 pm

  #1657743

  yogi123 (10/10/2013)

  ---

  Hello

  I need to Create a Function who pulls data from string and load to the table. here is sample string

  'CHG_FEP_SVC_GRP_ID(1) :: Expected Value = 1 :: Actual Value = 20'

  now I need to load to Expected Value in Amt1 column and Actual Value in Amt2 Column which is as in below table

  ErrorAmt1Amt2

  CHG_FEP_SVC_GRP_ID(1) :: Expected Value = 1 :: Actual Value = 20120

  Please help me to that

  Thanks

  You can do this using the DelimitedSplit8K function. You can find the article about this by following the link in my signature about splitting strings.

  create function GetYourSplit(@SomeValue varchar(100))

  returns table with schemabinding as

  return

  with myCTE as

  (

  select REPLACE(@SomeValue, ' :: ', ':') as Error

  )

  select Error,

  MAX(case when ItemNumber = 2 then substring(Item, charindex('=', Item) + 2, 100) end) as Amt1,

  MAX(case when ItemNumber = 3 then substring(Item, charindex('=', Item) + 2, 100) end) as Amt2

  from myCTE

  cross apply dbo.DelimitedSplit8K(Error, ':')

  group by Error

  Notice that I made your function an iTVF. You didn't specify if you wanted this or a scalar function and since scalar functions perform so poorly I went with this route. One other caveat is that using REPLACE in the function call has been shown to have some pretty serious performance implications so I did the replace prior to that call with the cte.

  Now in order to use this you can do something along these lines.

  select * from GetYourSplit('CHG_FEP_SVC_GRP_ID(1) :: Expected Value = 1 :: Actual Value = 20')

  Or if say you have a table named Table1 with a column named Column1 you could do this.

  select *

  from Table1 t

  cross apply dbo.GetYourSplit(Column1) s

  Hope that helps.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• yogi123

  SSCrazy

  Points: 2932

  More actions

  October 11, 2013 at 9:15 am

  #1657951

  Sean Lange (10/10/2013)

  ---

  yogi123 (10/10/2013)

  ---

  Hello

  I need to Create a Function who pulls data from string and load to the table. here is sample string

  'CHG_FEP_SVC_GRP_ID(1) :: Expected Value = 1 :: Actual Value = 20'

  now I need to load to Expected Value in Amt1 column and Actual Value in Amt2 Column which is as in below table

  ErrorAmt1Amt2

  CHG_FEP_SVC_GRP_ID(1) :: Expected Value = 1 :: Actual Value = 20120

  Please help me to that

  Thanks

  You can do this using the DelimitedSplit8K function. You can find the article about this by following the link in my signature about splitting strings.

  create function GetYourSplit(@SomeValue varchar(100))

  returns table with schemabinding as

  return

  with myCTE as

  (

  select REPLACE(@SomeValue, ' :: ', ':') as Error

  )

  select Error,

  MAX(case when ItemNumber = 2 then substring(Item, charindex('=', Item) + 2, 100) end) as Amt1,

  MAX(case when ItemNumber = 3 then substring(Item, charindex('=', Item) + 2, 100) end) as Amt2

  from myCTE

  cross apply dbo.DelimitedSplit8K(Error, ':')

  group by Error

  Notice that I made your function an iTVF. You didn't specify if you wanted this or a scalar function and since scalar functions perform so poorly I went with this route. One other caveat is that using REPLACE in the function call has been shown to have some pretty serious performance implications so I did the replace prior to that call with the cte.

  Now in order to use this you can do something along these lines.

  select * from GetYourSplit('CHG_FEP_SVC_GRP_ID(1) :: Expected Value = 1 :: Actual Value = 20')

  Or if say you have a table named Table1 with a column named Column1 you could do this.

  select *

  from Table1 t

  cross apply dbo.GetYourSplit(Column1) s

  Hope that helps.

  Excellent

  This is Great !!

  thanks for Help.

  You save my life. Much Much Appreciate.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  October 11, 2013 at 9:57 am

  #1657962

  yogi123 (10/11/2013)

  Excellent

  This is Great !!

  thanks for Help.

  You save my life. Much Much Appreciate.

  You are very welcome. Make sure you read that article and understand what the splitter function is doing.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• yogi123

  SSCrazy

  Points: 2932

  More actions

  October 11, 2013 at 12:08 pm

  #1657994

  Sean Lange (10/11/2013)

  ---

  yogi123 (10/11/2013)

  Excellent

  This is Great !!

  thanks for Help.

  You save my life. Much Much Appreciate.

  You are very welcome. Make sure you read that article and understand what the splitter function is doing.

  Hi Sean

  I need one favor from your end

  If the string is as below

  ('CHG_SERVICE_TYPE_CD(1) :: Expected Value = 9A0 :: Actual Value = 600 CHG_FEP_SVC_ID(1) :: Expected Value = 169 :: Actual Value = 745 CHG_FEP_SVC_GRP_ID(1) :: Expected Value = 4 :: Actual Value = 13 CHG_FEP_CIRCUMSTN_ID(1) :: Expected Value = 3 :: Actual Value = 4')

  Then I need to load Expected Value to Amt1

  and Actual Value to Amt2

  is it possible to do ?

  Thanks

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  October 15, 2013 at 8:40 am

  #1658559

  yogi123 (10/11/2013)

  ---

  Sean Lange (10/11/2013)

  ---

  yogi123 (10/11/2013)

  Excellent

  This is Great !!

  thanks for Help.

  You save my life. Much Much Appreciate.

  You are very welcome. Make sure you read that article and understand what the splitter function is doing.

  Hi Sean

  I need one favor from your end

  If the string is as below

  ('CHG_SERVICE_TYPE_CD(1) :: Expected Value = 9A0 :: Actual Value = 600 CHG_FEP_SVC_ID(1) :: Expected Value = 169 :: Actual Value = 745 CHG_FEP_SVC_GRP_ID(1) :: Expected Value = 4 :: Actual Value = 13 CHG_FEP_CIRCUMSTN_ID(1) :: Expected Value = 3 :: Actual Value = 4')

  Then I need to load Expected Value to Amt1

  and Actual Value to Amt2

  is it possible to do ?

  Thanks

  Yeah no big deal. Just extend the function to have 2 more columns. This is starting to sound like it might need to be a dynamic number of columns. You can use the concepts already posted to get you started. Then take a look at the link in my signature about dynamic cross tabs, that is where you are headed. The learning curve is a bit steep but once you get it you won't have any problems. Post back if you run into a snag and need some help.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/