PROBLEM IN STORED PROCEDURE SUBSTRING :((

[dotnetcsharpdev]

i have a simple stored procedure   and i want to split postcode in it which could be of length either 7 or 6 and tha second part will always be of length 3. am pasting the sql here. and also the error message. Plz help me resolve this error <code> SELECT          CONVERT(varchar, DecryptByKey(Postcode)) as Postcode,           CONVERT(varchar, DecryptByKey(Postcode)) as Postcode1,           CONVERT(varchar, DecryptByKey(Postcode)) as Postcode2,                                                                        if Postcode.length = 7                          begin                               set Postcode1= substring(Postcode,5,7)                               set Postcode1= substring(Postcode,1,4)                          end                     else                          begin                               set Postcode1 = substring(Postcode,4,6)                               set Postcode1 = substring(Postcode,1,3)                                                   end            &n

[Ashfield]

What database are you using? The syntax for if Postcode.length = 7 doesn't look like it will work - certainly not in sql server. In sql server it would be

SELECT CONVERT(varchar, DecryptByKey(Postcode)) as Postcode,
CONVERT(varchar, DecryptByKey(Postcode)) as Postcode1,
CONVERT(varchar, DecryptByKey(Postcode)) as Postcode2,

POSTCODE1 = case when len(postcode) = 7 then substring(Postcode,5,7) else substring(Postcode,4,6) end,
postcode2 = case when len(postcode) = 7 then substring(Postcode,1,4) else substring(Postcode,1,3)
FROM xyz
WHERE (abc = @def)

hope this helps

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[dotnetcsharpdev]

thanx Ashfield for ur reply I figured it out earlier itself and have implemented it almost the same way. copying my code incase it helps anyone else. <pre> LEN(CONVERT(varchar, DecryptByKey(Postcode))) as Postcodeln,      case when LEN(CONVERT(varchar, DecryptByKey(Postcode))) = '7' then             substring(CONVERT(varchar, DecryptByKey(Postcode)),1,4)      else           substring(CONVERT(varchar, DecryptByKey(Postcode)),1,3)      end Postcode1,      case when LEN(CONVERT(varchar, DecryptByKey(Postcode))) = '6' then             substring(CONVERT(varchar, DecryptByKey(Postcode)),4,6)      else           substring(CONVERT(varchar, DecryptByKey(Postcode)),5,7)      end Postcode2, </pre> Many Thanks

[Ashfield]

No problem, glad to have helped

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP