Cutting a Column Apart

• Tim Braga

  Newbie

  Points: 1

  More actions

  January 16, 2007 at 2:19 pm

  #171049

  Hi All,

   

  I have this figured out in Crystal but can't seem to get it to work in SQL.  I have a column where I want to grab everything but the first 19 characters.  In Crystal I am using this command:

  TrimRight({Incident_Details.Description}[20 to 35])

  But I cannot find a similar command in SQL, I was thinking of a left and/or right but that wont work because the characters at the end of the field could be 6, 8, or more characters. 

  Anyone???

  Thanks!

  Tim

• SDM

  SSCommitted

  Points: 1581

  More actions

  January 16, 2007 at 9:44 pm

  #683645

  How about

  substring(Incident_Details.Description, 20, len(Incident_Details.Description) -19)

  That should work, but beware of columns with length < 20

   

  Stephen

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 17, 2007 at 9:18 am

  #683778

  Stephen's right...you could use a case statement to avoid the < 20 pitfall:

  CASE WHEN len(Incident_Details.Description) < 20 then '' ELSE

  substring(Incident_Details.Description, 20, len(Incident_Details.Description) -19) END AS DESCRIPTION

  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!

• Prasad Bhogadi

  SSCrazy Eights

  Points: 9235

  More actions

  January 19, 2007 at 9:18 am

  #684356

  Infact RIGHT function should also work

  SELECT

  CASE WHEN LEN(Incident_Details.Description) > 19 THEN RIGHT(Incident_Details.Description,LEN(Incident_Details.Description)-19) ELSE '' END AS Description

  FROM

  Incident_Details

  Prasad Bhogadi
  www.inforaise.com