TSQL Functions

• Paul Cresham

  Hall of Fame

  Points: 3623

  More actions

  June 14, 2005 at 8:47 am

  #565349

  Please excuse my terminology... but happy it seemed to make sense

• Carl Rimmer

  SSCarpal Tunnel

  Points: 4068

  More actions

  June 14, 2005 at 8:50 am

  #565353

  I have ran the original code and it does differ to the count I get using my own code.  I have checked and it seems to exclude NULLS.

   

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  June 14, 2005 at 8:50 am

  #565352

  NP... that concept is hard to grasp.

• kpspencer

  Mr or Mrs. 500

  Points: 586

  More actions

  June 15, 2005 at 2:34 am

  #565576

  I have to work with PostCodes regularly. We have a problem where users input incomplete postcodes as well as incorrectly formatted postcodes.

  I have created the following view which looks at the postcodes and stips out just the postcode region. Makes allowances for london postcodes which differ from the rest of the country. If the postcode does not conform to Royalmail standards then returns PostCodeError

   

  A bit messy but it works

  Karl

  CREATE VIEW dbo.Conn_Clients_PostCode_Region

  AS

  SELECT     Client_Ref, Post_Code,

   

   PostCodeRegion= CASE

  -- Test for null or Blank!

  When Post_Code is null then 'PostCodeError'--'Post_Code_Error Null'

  When Post_Code ='' then 'PostCodeError'--'Post_Code_Error Blank'

  -- illegal char

  when  (Post_Code LIKE '%[`!"£$%^&*()_+#''*?_;,./]%' ) then  'PostCodeError'--'Post_Code Illeagal Char'

  -- starts with number

  when  substring(ltrim(Post_Code),1,1) not LIKE '[a-z]' then  'PostCodeError'--'Post_Code Illeagal first Char'

  -- Second char can't be 0 with number

  when  substring(ltrim(Post_Code),2,1) = '0' then  'PostCodeError'--'Post_Code Illeagal second Char'

   

  WHEN

  -- No Space, Just first Part

    (SELECT     CHARINDEX(' ', ltrim(Post_Code)) ) = 0  and (len(ltrim(Post_Code))) < 5 THEN

    -- Test for London!, Last Char would be letter if so get chars before last letter'

    Case

     -- Not letter

     When isnumeric(SUBSTRING ( ltrim(Post_Code) , Len(ltrim(Post_Code)) , 1 )) = 1 then ltrim(Post_Code)

     -- Is Letter

     When isnumeric(SUBSTRING ( ltrim(Post_Code) , Len(ltrim(Post_Code)) , 1 )) = 0 then

     -- But is second to last a number if not then error

      Case

      When isnumeric(SUBSTRING ( ltrim(Post_Code) , Len(ltrim(Post_Code))-1 , 1 )) = 1 then  

      

     SUBSTRING (ltrim(Post_Code) , 1 , Len(ltrim(Post_Code))-1 )

     Else 'PostCodeError'--'Post_Code_Error no number'

     end

     end

  WHEN

  -- No Space, Just first Part but longer than 4 char

    (SELECT     CHARINDEX(' ', ltrim(Post_Code)) ) = 0 and (len(ltrim(Post_Code))) > 4 THEN

   'PostCodeError'--'Error'

  --Correctly formed 

  WHEN

    (SELECT     CHARINDEX(' ', ltrim(Post_Code)) ) > 0

   and (SELECT     CHARINDEX(' ', ltrim(Post_Code)) )< 6

  THEN

    

    --LEFT(ltrim(Post_Code), (CHARINDEX(' ', ltrim(Post_Code)) - 1))

  -- Test for London!, Last Char would be letter if so get chars before last letter'

    Case

     -- Not letter

     When isnumeric(SUBSTRING ( ltrim(Post_Code) , (CHARINDEX(' ', ltrim(Post_Code))-1 ) , 1)) = 1 then

     SUBSTRING (ltrim(Post_Code) , 1 , (CHARINDEX(' ', ltrim(Post_Code))) )

     -- Is Letter

     When isnumeric(SUBSTRING ( ltrim(Post_Code) , (CHARINDEX(' ', ltrim(Post_Code))-1 ) , 1 )) = 0 then

      --SUBSTRING (ltrim(Post_Code) , 1 , (CHARINDEX(' ', ltrim(Post_Code))-2) )

     -- is char before letter a number

      Case

      When isnumeric(SUBSTRING ( ltrim(Post_Code) , (CHARINDEX(' ', ltrim(Post_Code))-2 ) , 1 )) = 0 then

       'PostCodeError'

      else

      SUBSTRING (ltrim(Post_Code) , 1 , (CHARINDEX(' ', ltrim(Post_Code))-2) )

     end

     End

  WHEN

    (SELECT     CHARINDEX(' ', ltrim(Post_Code)) ) > 4  THEN 'PostCodeError'--'Post_Code_Error no number'

    

  END

  FROM        Clients

   

• Carl Rimmer

  SSCarpal Tunnel

  Points: 4068

  More actions

  June 15, 2005 at 2:39 am

  #565577

  Thanks everyone for your help. Its much appreciated.

   

  Carl

• Peter E. Kierstead

  SSCarpal Tunnel

  Points: 4114

  More actions

  June 27, 2005 at 9:54 am

  #568664

  Try:

  Select Left(@Str,CharIndex(' ',@Str+' ')-1)

  ---

  
  PeteK
  I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

• Muhammed Sajid Hussain

  SSC Veteran

  Points: 290

  More actions

  June 28, 2005 at 1:50 am

  #568836

  Test Msg

• Muhammed Sajid Hussain

  SSC Veteran

  Points: 290

  More actions

  June 28, 2005 at 2:05 am

  #568840

  Hi All,

  Kindly can you please suggest me for the following doubts.

  What's the future of SQL Server 2000 Administration & MCDBA.

  Does it required for students to upgrade their edition from SQL Server 2000

  to SQL Server 5000.

  Kindly advice me is it good to study or not...

  Thanks in advance

  Best Regards