special query

• fldoctora

  SSC Rookie

  Points: 39

  More actions

  November 13, 2002 at 10:44 pm

  #79053

  i have a select statement and i want trim down some characters in the result of my data

  can you please give me the correct select statement for this

  Sample:

  select cust

  from customer

  and the result

  cust

  ------

  john pratts

  leny pratts

  jen pratts

  jeffrey pratts

  (4 rows affected)

  i want to trim the "PRATTS"

  in all of my results

  sample:

  cust

  ------

  john

  leny

  jen

  jeffrey

  (4 rows affected)

  hope you cn give me some idea ...

  thanks

  doc

  doc

  
  doc

• Arvind-57714

  SSCrazy

  Points: 2529

  More actions

  November 13, 2002 at 11:25 pm

  #441315

  select substring(name,1,charindex(' ',name)) as Name from t1

  Try this it will give the first word in the column before the blankspace where name is the columnname and ' ' is the separator

  Arvind

  
  Arvind

• Arvind-57714

  SSCrazy

  Points: 2529

  More actions

  November 13, 2002 at 11:26 pm

  #441316

  select substring(name,1,charindex(' ',name)) as Name from t1

  Try this it will give the first word in the column before the blankspace where name is the columnname and ' ' is the separator

  Arvind

  
  Arvind

• Arvind-57714

  SSCrazy

  Points: 2529

  More actions

  November 13, 2002 at 11:28 pm

  #441317

  select substring(name,1,charindex(' ',name)) as Name from t1

  Try this it will give the first word in the column before the blankspace where name is the columnname and ' ' is the separator

  You can also use the string function Left() if you know the number of characters you want to trim from the word and it works only if the characters are the same. Better try the first one!!

  Arvind

  
  Arvind

• Antares686

  SSC Guru

  Points: 125444

  More actions

  November 14, 2002 at 4:16 am

  #441330

  If however you know that it is PRATTS you need to remove from every instance and does not change then this will also work, but Arvind's is most likely what you are looking for.

  SELECT REPLACE(cust, 'PRATTS', '')

  Note thou if they have a middle initial you want to keep or just looking to remove last name, you could use len with left and reverse like this.

  SELECT LEFT(cust, LEN(cust) - CHARINDEX(' ', REVERSE(cust)))