create function

• river1

  SSC-Insane

  Points: 24315

  More actions

  September 17, 2010 at 3:05 am

  #146967

  HI,

  I need to create a function that receives a parameter (string) and returns a value if there is a second '*' in the string.

  E.g:

  Function receives the string:

  *4112*21211222

  returns number 6

  Other e.g

  Function receives the string:

  *411221211*222

  returns number 11

  Other e.g

  Function receives the string:

  411221211222

  returns nothing, because there is no second *

  Can someone help me with the t-sql to do this?

  Thnak you

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  September 17, 2010 at 3:17 am

  #1222836

  declare @SearchString varchar(100)

  set @SearchString = '*411221211*222'

  select charindex('*',@SearchString,CHARINDEX('*',@SearchString,1)+1)

• hallidayd

  SSCrazy

  Points: 2490

  More actions

  September 17, 2010 at 3:19 am

  #1222837

  IF LEN(@string) - LEN(REPLACE(@string, '*', '')) >= 2

  BEGIN

  RETURN LEN(@string) - CHARINDEX('*', REVERSE(@string)) + 1

  END

• Oliiii

  SSCertifiable

  Points: 5328

  More actions

  September 17, 2010 at 3:19 am

  #1222838

  Use this:

  Select CHARINDEX('*',@InputString,CHARINDEX('*',@InputString, 1) + 1)

  Returns 0 if it doesn't find a second '*'.

  edit: too late

• river1

  SSC-Insane

  Points: 24315

  More actions

  September 17, 2010 at 4:00 am

  #1222867

  thank you very much