If data value has 8 characters, show middle six.

• Erin.

  SSCertifiable

  Points: 5237

  More actions

  September 25, 2008 at 12:54 pm

  #193587

  I am trying to pull a field where if the data value has 8 characters, strip off the first and last character. If data value has 6 characters, leave as is. The data type for this field is varchar(20).

  Right now I have

  select substring(columnname,2,6)

  where len(columnname) = 8

  This works if the field has 8 characters, but how can I account for the column if it has 6 or 7 characters?

• upperbognor

  Right there with Babe

  Points: 767

  More actions

  September 25, 2008 at 1:12 pm

  #877287

  Declare @ColumnName varchar(20)

  Set @ColumnName = 'abcdefgh'

  select

  Case

  When Len(@ColumnName) = 8 Then substring(@ColumnName,2,6)

  --Assumes you want to remove the last charater at a length of 7

  When Len(@ColumnName) = 7 Then substring(@ColumnName,1,6)

  When Len(@ColumnName) = 6 Then @ColumnName

  Else @ColumnName

  End As ColumnName

  Though what about if your field has more then 8 charaters? as it's a varchar(20)

• Lowell

  SSC Guru

  Points: 323482

  More actions

  September 25, 2008 at 1:12 pm

  #877288

  it depends on what you want to do, of course....say that if it's 7 characters, do you you do anything at all? do you only strip off the first character, or strip the last, or ignore because it doesn't fit the pattern?

  if it's 6, you said to leave it alone correct? since the field is a varchar(20), what if it is greater than 8 characters?

  if yo0u can define the business rule, we can help on how to get the desired results.

  Erin (9/25/2008)

  ---

  I am trying to pull a field where if the data value has 8 characters, strip off the first and last character. If data value has 6 characters, leave as is. The data type for this field is varchar(20).

  Right now I have

  select substring(columnname,2,6)

  where len(columnname) = 8

  This works if the field has 8 characters, but how can I account for the column if it has 6 or 7 characters?

  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!

• Luxgilby

  SSCrazy

  Points: 2097

  More actions

  October 2, 2008 at 4:55 am

  #879870

  The following covers for a lot:

  DECLARE @tstString VARCHAR(255)

  SET @tstString = '20080528 '

  SELECT

  CASE

  WHEN LEN(LTRIM(RTRIM(@tstString))) >= 6 AND LEN(LTRIM(RTRIM(@tstString))) <= 8

  THEN RIGHT(LTRIM(RTRIM(@tstString)), 6)

  ELSE 'ERR'

  END

  The CASE statement validates the length of the varchar excluding leading and trailing spaces. If the field is of the right length, take the last 6 characters of the string.

  FYI:

  - SELECT RIGHT('123', 6) will return a varchar of lenght 3.

  - SELECT RIGHT(NULL, 6) will return NULL.