Post reply

Function that concatenate for multiple types and evaluates field length

  • April 5, 2016 at 2:36 pm

    #312918

    Hello,

    I am trying to create a function that will:

    1. Concatenate email by email type

    2. Limit the string for up to 150 characters. If an email makes the sting go over 150 charcters, than that last email will be removed from the list. (this way we do not have any incomplete emails in the string)

    Here is my code (though I know it is not right):

    Declare@RETBILLvarchar(150),

    @RETMARKvarchar(150),

    @CIDX int,

    @MIDX int

    Set@RETBILL = ''

    Set@RETMARK = ''

    Declare @EMAIL_TEMP Table(IDX int identity, EMAIL_STR varchar(150))

    Insert Into @EMAIL_TEMP(EMAIL_STR)

    Select EmailAddr From Custy Where ID = @ID AND Email_Type='Billing' or Email_Type='Marketing'

    Set @MIDX=@@rowcount

    Set @CIDX=1

    While @CIDX<=@MIDX

    Begin

    If @CIDX=1

    Select @RETBILL = EMAIL_STR From @EMAIL_TEMP Where IDX=@CIDX and Email_Type='Billing'

    Select @RETMARK = EMAIL_STR From @EMAIL_TEMP Where IDX=@CIDX and Email_Type='Marketing'

    Else

    Select @RETBILL = @RETBILL + ';' + EMAIL_STR From @EMAIL_TEMP Where IDX=@CIDX and Email_Type='Billing'

    Select @RETBILL = @RETMARK + ';' + EMAIL_STR From @EMAIL_TEMP Where IDX=@CIDX and Email_Type='Marketing'

    Set @CIDX=@CIDX +1

    End

    Return @RETBILL

    Return @RETMARK

  • April 5, 2016 at 3:02 pm

    #1870004

    not sure why you might limit yourself to 150 chars; database mail, outlook, email clients do not have a limitation like that.

    i'm just selecting emails via FOR XML to make a sample string.

    the rest is the logic to limit the seach

    since you are delimiting emails by semicolumn, just search fro the semicolon that is less than or equal to 150, in the reverse of the string.

    declare @EmailString varchar(max);

    SELECT @EmailString = Colzs

    FROM(SELECT

    Colzs = STUFF((SELECT ';' + 'sys@' + name + '.com '

    FROM sys.tables

    ORDER BY object_id

    FOR XML PATH('')

    ),1,1,'')

    ) s

    select @EmailString

    --now the 150 char rule.

    select @EmailString = substring(@EmailString,1,150) --might be in the middle

    select @EmailString

    if RIGHT(@EmailString,1) <> ';'

    SELECT REVERSE(SUBSTRING(reverse(@EmailString),CHARINDEX(';',reverse(@EmailString)),150))

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 6, 2016 at 1:55 pm

    #1870299

    thank you very much, this is very helpful!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply