Stored Procedure

• patla4u

  SSCrazy

  Points: 2103

  More actions

  May 12, 2011 at 6:31 pm

  #238202

  I have to create a store procudure....below is description..

  I have a IDcode ,,,,,and length is 6.

  like

  00000}

  00000A

  00000B

  12323C

  .........

  .........

  Now I want to change last digit means I have to put 0 instead of }

  put 1 instead of A

  for each row...

  below is description ,,,I want to change last digit,

  { 0

  A 1

  B 2

  C 3

  D 4

  E 5

  F 6

  G 7

  H 8

  I 9

  and

  } 0

  J -1

  K -2

  L -3

  M -4

  N -5

  O -6

  P -7

  Q -8

  R -9

  How can i do with stored procedure and I also want to do with SSIS package ...like derived column??

  Please Help me,

  Thanks

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  May 12, 2011 at 7:29 pm

  #1324055

  Use the REPLACE fimction

  REPLACE(find, replace, string)

  find

  Specifies the string that contains the substring to replace all instances of with another.

  replace

  Specifies the substring to locate.

  string

  Specifies the substring with which to replace the located substring.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• dyfhid

  SSC Enthusiast

  Points: 160

  More actions

  May 12, 2011 at 7:34 pm

  #1324057

  REPLACE will work, of course, but direction is needed as to what to REPLACE *with*.

  I'm no expert, but I played around a bit with your requirements, and I think at least part of the answer is thus:

  Declare @IDCode char(6)

  set @IDCode = '12323C'

  select ((ascii(right(@IDCode,1))%65)+1)

  This works well for 'A' through 'I', so some test would have to be performed for higher letters going into more digits, and, of course, this doesn't account for the case of '}'

  Hope this helps a little bit, anyway.