Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] trim 3 characters from right of string

Expand Messages
  • Daniel Wilson
    UPDATE TESTTABLE SET Name = LEFT(Name, CHARINDEX( , Name)) + , + RIGHT(RTRIM(Name), LEN(Name) - CHARINDEX( , Name, CHARINDEX( , Name) +
    Message 1 of 15 , Feb 17, 2004
    • 0 Attachment
      Message
      UPDATE    TESTTABLE
      SET              Name = LEFT(Name, CHARINDEX(' ', Name)) + ',' + RIGHT(RTRIM(Name), LEN(Name) - CHARINDEX(' ', Name, CHARINDEX(' ', Name) + 1))
       
      Glad it helped you out Nilesh.
       
      Best Regards,
      Daniel
      -----Original Message-----
      From: nilesh babar [mailto:nilesh_babar2003@...]
      Sent: Tuesday, February 17, 2004 12:01 AM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: RE: [SQLQueriesNoCode] trim 3 characters from right of string

      Hi,
          Thank you very much, this query will solve my work littile bit. Actualy I want to update perticular column buts its ok.
      Thanks once agin.
       
      Thanks & Regards,
      Nilesh

      Daniel Wilson <danwilson@...> wrote:
      SELECT     LEFT(Name, CHARINDEX(' ', Name)) + ',' + RIGHT(RTRIM(Name), LEN(Name) - CHARINDEX(' ', Name,CHARINDEX(' ', Name) + 1)) AS 'NewName'
      FROM         TESTTABLE
       
      Here is a query to do what you ask except that it is a select statement instead of an UPDATE query.  It should be easy enough to convert.  However, I wanted to point out a few things:  This is a TSQL query,  Notice I use RTRIM(Name) because I wasn't sure if you were using a fixed length string column (I assumed you were since it was the more "complex" one).  Before you convert and run this I want to make one small disclaimer.  Make sure the values in your column adhere to the standard mentioned below.  I.E. there are no double spaces, missing middle names (actually if there is a missing middle name and two spaces this should work but not if there is only one...)  Anyways the point is - make sure this query will work for all of your data not just the expected case.  My final piece of advice is that if your realize there is more varience in your data than expected use a programming language as opposed to a set based language like SQL to do your data manipulation since the programming language is better at string based manipulation and dealing with the irregular cases.
       
      HTH,
      Daniel
      -----Original Message-----
      From: nilesh babar [mailto:nilesh_babar2003@...]
      Sent: Saturday, February 14, 2004 2:04 AM
      To: SQLQueriesNoCode@yahoogroups.com
      Cc: Daniel Wilson
      Subject: RE: [SQLQueriesNoCode] trim 3 characters from right of string

      Hi,

      I have one query.

      I have one column called 'name' which contain name like 'John Danial Hank' meanse first_name,mid_name,Last_name' but i want only first_name,Last_name i dont want mid_name in same column right now, which query i have to use for update 'name' column?

      Please give me proper answer for this problem.

      /r

      nilesh


      Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now


      This message was scanned by McAfee Webshield.


      Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now


      This message was scanned by McAfee Webshield.

    • jai ganesh
      Ok , I have seen your problem ,the remedy for that is you have use replace function, It will be like this If you are using Asp as front end. Solution: dim
      Message 2 of 15 , Feb 17, 2004
      • 0 Attachment
        Ok ,
        I have seen your problem ,the remedy for that is
        you have use replace function, It will be like this
        If you are using Asp as front end.

        Solution:
        dim data,FilterData
        data="3759350950938850489.00"
        FilterData=replace(data,".00","")

        with regards
        jai ganesh.D

        ________________________________________________________________________
        Yahoo! India Insurance Special: Be informed on the best policies, services, tools and more.
        Go to: http://in.insurance.yahoo.com/licspecial/index.html
      • nilesh babar
        Hi, Thanks for giving this query this query solved my problem i use your query for updating table. The query i given bellow, UPDATE a set a.name = b.NewName
        Message 3 of 15 , Feb 18, 2004
        • 0 Attachment
          Hi,
          Thanks for giving this query this query solved my problem i use your query for updating table. The query i given bellow,
           
          UPDATE a set a.name = b.NewName
          from au1 a,
          (SELECT     au_id,LEFT(Name, CHARINDEX(' ', Name)) + '' + RIGHT(RTRIM(Name), LEN(Name) - CHARINDEX(' ', Name,CHARINDEX(' ', Name) + 1)) AS 'NewName'
          FROM         au1) b 
          WHERE a.au_id=b.au_id
           
          Thanks & Regard
          Nilesh

          Daniel Wilson <danwilson@...> wrote:
          SELECT     LEFT(Name, CHARINDEX(' ', Name)) + ',' + RIGHT(RTRIM(Name), LEN(Name) - CHARINDEX(' ', Name,CHARINDEX(' ', Name) + 1)) AS 'NewName'
          FROM         TESTTABLE
           
          Here is a query to do what you ask except that it is a select statement instead of an UPDATE query.  It should be easy enough to convert.  However, I wanted to point out a few things:  This is a TSQL query,  Notice I use RTRIM(Name) because I wasn't sure if you were using a fixed length string column (I assumed you were since it was the more "complex" one).  Before you convert and run this I want to make one small disclaimer.  Make sure the values in your column adhere to the standard mentioned below.  I.E. there are no double spaces, missing middle names (actually if there is a missing middle name and two spaces this should work but not if there is only one...)  Anyways the point is - make sure this query will work for all of your data not just the expected case.  My final piece of advice is that if your realize there is more varience in your data than expected use a programming language as opposed to a set based language like SQL to do your data manipulation since the programming language is better at string based manipulation and dealing with the irregular cases.
           
          HTH,
          Daniel
          -----Original Message-----
          From: nilesh babar [mailto:nilesh_babar2003@...]
          Sent: Saturday, February 14, 2004 2:04 AM
          To: SQLQueriesNoCode@yahoogroups.com
          Cc: Daniel Wilson
          Subject: RE: [SQLQueriesNoCode] trim 3 characters from right of string

          Hi,

          I have one query.

          I have one column called 'name' which contain name like 'John Danial Hank' meanse first_name,mid_name,Last_name' but i want only first_name,Last_name i dont want mid_name in same column right now, which query i have to use for update 'name' column?

          Please give me proper answer for this problem.

          /r

          nilesh


          Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now


          This message was scanned by McAfee Webshield.


          Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now

        Your message has been successfully submitted and would be delivered to recipients shortly.