none
Removing '.' from a column

    Question

  • Hi All,

    We have a table say TableA. It has a column 'Detail' which is varchar(250). I has some values with '.' in end and some without '.' Example (Row 1 : 'ADB', Row2 : 'ADC. DCD.' Row3 : 'ADC.')

    We need to remove the '.' which is occurring only in the last of 'Detail' column and load it. Example after removing '.' (Row 1: 'ADB', Row2 : 'ADC. DCD', Row3 : 'ADC')

    Database is SQL Server 2008 R2 and we are inserting into the TableA using a INSERT INTO 'SELECT' query..

    Please suggest.

    Thanks

    Jim

    Friday, October 18, 2013 6:49 AM

Answers

  • Hi Jim

    Please try this query

    SELECT REVERSE(SUBSTRING(REVERSE(detail), PatIndex('%[A-Z[0-9][a-z]%', REVERSE(Detail)), LEN(detail))) FROM dbo.TableA


    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

    • Proposed as answer by B3nt3n Monday, October 21, 2013 6:31 PM
    • Marked as answer by Jim123456789jim Wednesday, October 23, 2013 2:49 PM
    Monday, October 21, 2013 5:33 PM

All replies

  • Hi Jim,

    Here is a query that will give you desired results (column DetailResult).

    use test
    
    if object_id('dbo.TableA') is not null drop table dbo.TableA
    
    create table dbo.TableA
    (
    	Detail varchar(250)
    )
    
    insert into dbo.TableA (Detail)
    values ('ADB'), ('ADC. DCD.'), ('ADC.'), ('ADC. ABC')
    
    select
    	a.Detail as Detail
    	, reverse (a.Detail) as DetailReverse
    	, (case charindex ('.', reverse(a.Detail), 1)
    			when 1 then left(a.Detail, len(a.Detail) - 1)
    			else a.Detail
    		end) as DetailResult
    from dbo.TableA a
    


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Proposed as answer by Harry Bal Friday, October 18, 2013 2:02 PM
    Friday, October 18, 2013 7:04 AM
  • This is Perfect Dean! but there is a small change. There should not be any special character at the end. Could you pls suggest..
    Friday, October 18, 2013 3:32 PM
  • Thanks Hary! This is perfect. A small change, if the column is ending with any special character that need to be removed. Also, the column should not end with a special character at all.. Please suggest

    Sample data

    'ABC DS.'

    'AD.'

    'DG@'

    'DGC DS '

    'fdsf fs/'

    'fs fds //'

    Friday, October 18, 2013 4:13 PM
  • This query may be useful

    SELECT
     CASE
      WHEN ASCII(RIGHT(Detail, 1)) BETWEEN 32 AND 47 OR ASCII(RIGHT(Detail, 1)) BETWEEN 58 AND 64 THEN LEFT(Detail, LEN(Detail)-1)
      ELSE
      Detail
     END AS
     'Detail'
    from dbo.TableA a


    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

    Friday, October 18, 2013 4:25 PM
  • Thanks Ayyappan.But if the second last character is also a special character then I think this will not work..Need to check in my sql machine..
    Friday, October 18, 2013 5:36 PM
  • Hi Jim

    Please try this query

    SELECT REVERSE(SUBSTRING(REVERSE(detail), PatIndex('%[A-Z[0-9][a-z]%', REVERSE(Detail)), LEN(detail))) FROM dbo.TableA


    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

    • Proposed as answer by B3nt3n Monday, October 21, 2013 6:31 PM
    • Marked as answer by Jim123456789jim Wednesday, October 23, 2013 2:49 PM
    Monday, October 21, 2013 5:33 PM
  • Thanks Ayyappan. It worked!!
    Wednesday, October 23, 2013 2:50 PM