How to Split a char field?

• AB-144632

  SSC Veteran

  Points: 287

  More actions

  December 9, 2005 at 10:23 am

  #92684

  Hi

  I do not know if it is possible but, I need find how to split a fleld in SQL server. I need to move the last three characters to another field on their own.

  Is there a way to do this?  

  the field is char (60) but the last three characters are not always on the same string position.

  Thanks

   

  AB

   

• chandra pottipati

  SSC Veteran

  Points: 292

  More actions

  December 9, 2005 at 10:33 am

  #608758

  select substring(companyname,len(companyname) - 2,len(companyname)),companyname from customers

   

  this is a query that returns the last three characters from northwind database

• DavidT

  SSCommitted

  Points: 1727

  More actions

  December 9, 2005 at 10:34 am

  #608759

  I presume you just need to trim off the trailing spaces before you grab the last three characters:

   

  declare @Table table (col1 char(60), Col2 char(60))

  insert into @Table (Col1) values ('Test Test     123         ')

  insert into @Table (Col1) values ('Test Test Test     123                    ')

  insert into @Table (Col1) values ('Test Test Test  Test        123               ')

  select right(rtrim(Col1),3) from @Table

  Update @Table set Col2 = right(rtrim(Col1),3)

  select * from @table

   

  David

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  December 9, 2005 at 10:36 am

  #608760

  SELECT RIGHT(companyname, 3) AS lastThreeChars,companyname FROM Customers

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**

• AB-144632

  SSC Veteran

  Points: 287

  More actions

  December 9, 2005 at 10:38 am

  #608762

  Thanks guys,  Got it! and it works 

  I can actually do it either way.