Solved

Removing Unwanted Text From Column

Posted on 2003-12-01
22
492 Views
Last Modified: 2008-02-26
Hello, I have a table with a listing of files.  An exampe is as follows:

ACCOUNT_OWNERSHIP_DATA_1.TXT
ACCOUNT_TYPE_2.TXT
ACCOUNT_TYPE_DESC_3.TXT
ACTIVITY_REASON_TEXT_4.TXT
AC_PGM_FILES_CONTROL_5.TXT
AC_PROGRAMS_6.TXT
AC_USER_MESSAGES_7.TXT
ADJUSTMENT_REASON_8.TXT
ADR_STREET_SUFFIX_9.TXT
ADR_UNIT_DESIGNATOR_10.TXT
AGENCY_ACTV_RULES_11.TXT

I need to get rid of the _(number).txt ... the number is never constant as it goes up to 500 and something.  So, I need to delete _%.TXT ... I have some code that I thought would work but it ends up deleting everything before the .TXT  ... If someone could help me modify my existing code or provide another simpler way, I'd be most appreciative.  Thanks.

Here is my code:

WHILE EXISTS( SELECT * FROM #TempTableNames WHERE PATINDEX('_%.%', Col001) > 0 )
UPDATE #TempTableNames
SET Col001 = FixedColumn1
FROM #TempTableNames INNER JOIN
     (SELECT
        Col001,
        REPLACE( Col001, SUBSTRING(Col001, ConstPos, CHARINDEX('.', Col001, ConstPos) - ConstPos), '' ) AS FixedColumn1
     FROM
          (SELECT Col001, PATINDEX('_%.%', Col001) As ConstPos FROM #TempTableNames
          WHERE PATINDEX('_%.%', Col001) > 0) AS ConstTable) AS UpdatedColumns
ON #TempTableNames.Col001 = UpdatedColumns.Col001
0
Comment
Question by:Roxanne25
  • 11
  • 10
22 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9851756
I THINK THIS DOES IT...

assuming that you haven't got multiple files per row...

UPDATE #TempTableNames
SET Col001 = rEVERSE(SUBSTRING(LEFTBIT,NUMSTART,DATALENGTH(LEFTBIT) - nUMSTART))
              + rIGHTBIT
FROM #TempTableNames INNER JOIN
        FROM (
        Select z.*
         from (
        Select col001,patindex('_',leftbit) as numstart,rightbit,constpos
         From (
       Select col001,reverse(left(col001,constpos - 1 ) as leftbit,constpos
       , substr(col001,constpos + 1,Datalength(col001) - constpos) as rightbit
          From  
          (SELECT Col001, PATINDEX('%.txt', Col001) As ConstPos FROM #TempTableNames
          WHERE PATINDEX('.txt', Col001) > 0) ) AS x
            ) as Y
          Where patindex('_',leftbit) > 0
         ) as Z
          Where Isnumeric(leftbit,1,numstart-1) = 1
          ) AS u
ON #TempTableNames.Col001 = U.Col001

0
 

Author Comment

by:Roxanne25
ID: 9851835
Hmm...thanks for your reply, but I got a lot of syntax errors when I tried to run it:

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'FROM'.
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near ')'.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9851860
this any better?

UPDATE #TempTableNames
SET Col001 = rEVERSE(SUBSTRING(LEFTBIT,NUMSTART,DATALENGTH(LEFTBIT) - nUMSTART))
              + rIGHTBIT
FROM #TempTableNames
INNER JOIN
       ( Select z.*
         from (
                Select col001,patindex('_',leftbit) as numstart,rightbit,constpos
                  From (
                         Select col001,reverse(left(col001,constpos - 1 ) as leftbit
                               ,constpos
                               , substr(col001,constpos + 1,Datalength(col001) - constpos)
                                 as rightbit
                           From  
                                (SELECT Col001,
                                     PATINDEX('%.txt', Col001) As ConstPos
                                   FROM #TempTableNames
                                   WHERE PATINDEX('.txt', Col001) > 0)
                                 ) AS x
                       ) as Y
                  Where patindex('_',leftbit) > 0
              ) as Z
          Where Isnumeric(leftbit,1,numstart-1) = 1
       ) AS u
ON #TempTableNames.Col001 = U.Col001



0
 

Author Comment

by:Roxanne25
ID: 9851875
Hehe, better but still two errors :)  I tried looking at it to see what the problem is, but I'm not too sure of the code you did... so I don't wanna mess it up. :)

Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 19
Line 19: Incorrect syntax near ')'.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9851928
how about this...
missing bracket on the LEFT


-- X  identify rows with *.Txt files....
-- Y  Reverse the left hand side of the string
--      so that  abc_500.txt   is now 005_cba
-- Z  confirm that a _ precedes the numeric bit...
-- U  confirm the first part of the reversed string is numeric
--
-- The update the col by reversing the reversed leftbit minus the numeric
--   and add back on any trailing characters after the .txt
--
-- hey presto!
 

UPDATE #TempTableNames
SET Col001 = rEVERSE(SUBSTRING(LEFTBIT,NUMSTART,DATALENGTH(LEFTBIT) - nUMSTART))
              + rIGHTBIT
FROM #TempTableNames
INNER JOIN
       ( Select z.*
         from (
                Select col001,patindex('_',leftbit) as numstart,rightbit,constpos
                  From (
                         Select col001,reverse(left(col001,constpos - 1 )) as leftbit
                               ,constpos
                               , substr(col001,constpos + 1,Datalength(col001) - constpos)
                                 as rightbit
                           From  
                                (SELECT Col001,
                                     PATINDEX('%.txt', Col001) As ConstPos
                                   FROM #TempTableNames
                                   WHERE PATINDEX('.txt', Col001) > 0)
                                 ) AS x  
                       ) as Y  
                  Where patindex('_',leftbit) > 0
              ) as Z
          Where Isnumeric(leftbit,1,numstart-1) = 1
       ) AS u
ON #TempTableNames.Col001 = U.Col001




0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9851951
sorry i think
SET Col001 = rEVERSE(SUBSTRING(LEFTBIT,NUMSTART,DATALENGTH(LEFTBIT) - nUMSTART))
              + rIGHTBIT

should be
SET Col001 = rEVERSE(SUBSTRING(LEFTBIT,NUMSTART + 1,DATALENGTH(LEFTBIT) - nUMSTART))
              + rIGHTBIT
0
 

Author Comment

by:Roxanne25
ID: 9851969
Ok, we're almost there!  Now I'm getting this error:

Server: Msg 174, Level 15, State 1, Line 23
The isnumeric function requires 1 arguments.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9852058
Where Isnumeric(leftbit,1,numstart-1) = 1

should be

Where Isnumeric(Left(leftbit,numstart-1)) = 1
       
       
0
 

Author Comment

by:Roxanne25
ID: 9852097
Its still not working... now I'm getting this error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'leftbit'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'LEFTBIT'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'LEFTBIT'.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9852193
sorry left a LEFTBIT out of the z table...

-- X  identify rows with *.Txt files....
-- Y  Reverse the left hand side of the string
--      so that  abc_500.txt   is now 005_cba
-- Z  confirm that a _ precedes the numeric bit...
-- U  confirm the first part of the reversed string is numeric
--
-- The update the col by reversing the reversed leftbit minus the numeric
--   and add back on any trailing characters after the .txt
--
-- hey presto!
 

UPDATE #TempTableNames
SET Col001 = rEVERSE(SUBSTRING(LEFTBIT,NUMSTART+1,DATALENGTH(LEFTBIT) - nUMSTART))
              + rIGHTBIT
FROM #TempTableNames
INNER JOIN
       ( Select z.*
         from (
                Select col001,patindex('_',leftbit) as numstart,rightbit,constpos,leftbit
                  From (
                         Select col001,reverse(left(col001,constpos - 1 )) as leftbit
                               ,constpos
                               , substr(col001,constpos + 1,Datalength(col001) - constpos)
                                 as rightbit
                           From  
                                (SELECT Col001,
                                     PATINDEX('%.txt', Col001) As ConstPos
                                   FROM #TempTableNames
                                   WHERE PATINDEX('.txt', Col001) > 0)
                                 ) AS x  
                       ) as Y  
                  Where patindex('_',leftbit) > 0
              ) as Z
          Where Isnumeric(left(leftbit,numstart-1)) = 1
       ) AS u
ON #TempTableNames.Col001 = U.Col001

0
 

Author Comment

by:Roxanne25
ID: 9852210
Good news... :)  There are no more errors in the code... bad news is:

0 Rows Affected.

Not sure what to do now....
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9852369
ok confirm the bits actually work

does this

return as expected?

SELECT top 2 Col001,  PATINDEX('%.txt', Col001) As ConstPos
                                   FROM #TempTableNames
                                   WHERE PATINDEX('.txt', Col001) > 0)

if so does this...
Select top 2 col001,reverse(left(col001,constpos - 1 )) as leftbit
                               ,constpos
                               , substr(col001,constpos + 1,Datalength(col001) - constpos)
                                 as rightbit
                           From  
                                (SELECT Col001,
                                     PATINDEX('%.txt', Col001) As ConstPos
                                   FROM #TempTableNames
                                   WHERE PATINDEX('.txt', Col001) > 0)
                                 ) AS x  
                       ) as Y  
                  Where patindex('_',leftbit) > 0
             
etc..
                       
0
 

Author Comment

by:Roxanne25
ID: 9852394
I ran those as you suggested, and it didn't return any rows at all.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9852416
post the results of this please
SELECT top 2 Col001,  PATINDEX('%.txt', Col001) As ConstPos
                                   FROM #TempTableNames
                                   
0
 

Author Comment

by:Roxanne25
ID: 9852428
ACCOUNT_OWNERSHIP_DATA_1.TXT      25
ACCOUNT_TYPE_2.TXT      15
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9852574
run this please
Select top 2 col001,reverse(left(col001,constpos - 1 )) as leftbit
                               ,constpos
                               , substr(col001,constpos + 1,Datalength(col001) - constpos)
                                 as rightbit
                           From  
                                (SELECT Col001,
                                     PATINDEX('%.txt', Col001) As ConstPos
                                   FROM #TempTableNames
                                   WHERE PATINDEX('.txt', Col001) > 0)
                                 ) AS x  
                       ) as Y  
                  Where charindex('_',leftbit) > 0
             
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9852619
i've changed the patindexes to charindexes    ... we don't really need to search...
the _ is a wildcard character as well (any single character) I think that was the last problem..
 

-- X  identify rows with *.Txt files....
-- Y  Reverse the left hand side of the string
--      so that  abc_500.txt   is now 005_cba
-- Z  confirm that a _ precedes the numeric bit...
-- U  confirm the first part of the reversed string is numeric
--
-- The update the col by reversing the reversed leftbit minus the numeric
--   and add back on any trailing characters after the .txt
--
-- hey presto!
 

UPDATE #TempTableNames
SET Col001 = rEVERSE(SUBSTRING(LEFTBIT,NUMSTART+1,DATALENGTH(LEFTBIT) - nUMSTART))
              + rIGHTBIT
FROM #TempTableNames
INNER JOIN
       ( Select z.*
         from (
                Select col001,Charindex('_',leftbit) as numstart,rightbit,constpos,leftbit
                  From (
                         Select col001,reverse(left(col001,constpos - 1 )) as leftbit
                               ,constpos
                               , substr(col001,constpos + 1,Datalength(col001) - constpos)
                                 as rightbit
                           From  
                                (SELECT Col001,
                                     charINDEX('.txt', Col001) As ConstPos
                                   FROM #TempTableNames
                                   WHERE charINDEX('.txt', Col001) > 0)
                                 ) AS x  
                       ) as Y  
                  Where charindex('_',leftbit) > 0
              ) as Z
          Where Isnumeric(left(leftbit,numstart-1)) = 1
       ) AS u
ON #TempTableNames.Col001 = U.Col001
0
 
LVL 4

Expert Comment

by:BulZeyE
ID: 9852681
I was working on a select statement to work this issue, which could easily be converted to an update statement.  Would the following work for you?

SELECT col001 AS Before,
       CASE
         WHEN ISNUMERIC(SUBSTRING(col001,(DATALENGTH(COL001) - CHARINDEX('_',REVERSE(col001))+2),CHARINDEX('.TXT',col001) - (DATALENGTH(COL001) - CHARINDEX('_',REVERSE(col001))) - 2)) = 1
             THEN SUBSTRING(col001,1,DATALENGTH(COL001) - CHARINDEX('_',REVERSE(col001))) + SUBSTRING(col001,CHARINDEX('.TXT',col001),DATALENGTH(col001) - CHARINDEX('.TXT',col001) + 2)
             ELSE col001
       END as After
  FROM #foo
 WHERE RIGHT(COL001,4) = '.TXT'

#foo was my working table, and here is the output:


Before                                                                                               After                                                                                                                                                                                                    
---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AC_PGM_FILES_CONTROL_5.TXT                                                                           AC_PGM_FILES_CONTROL.TXT
AC_PROGRAMS_6.TXT                                                                                    AC_PROGRAMS.TXT
AC_USER_MESSAGES_7.TXT                                                                               AC_USER_MESSAGES.TXT
ACCOUNT_OWNERSHIP_DATA_1.TXT                                                                         ACCOUNT_OWNERSHIP_DATA.TXT
ACCOUNT_TYPE_2.TXT                                                                                   ACCOUNT_TYPE.TXT
ACCOUNT_TYPE_DESC_3.TXT                                                                              ACCOUNT_TYPE_DESC.TXT
ACTIVITY_REASON_TEXT_4.TXT                                                                           ACTIVITY_REASON_TEXT.TXT
ADJUSTMENT_REASON_8.TXT                                                                              ADJUSTMENT_REASON.TXT
ADR_STREET_SUFFIX_9.TXT                                                                              ADR_STREET_SUFFIX.TXT
ADR_UNIT_DESIGNATOR_10.TXT                                                                           ADR_UNIT_DESIGNATOR.TXT
AGENCY_ACTV_RULES_11.TXT                                                                             AGENCY_ACTV_RULES.TXT
ZACCOUNT_OWNERSHIP_DATA_A.TXT                                                                        ZACCOUNT_OWNERSHIP_DATA_A.TXT
ZACCOUNT_TYPE.TXT                                                                                    ZACCOUNT_TYPE.TXT

(13 row(s) affected)
0
 

Author Comment

by:Roxanne25
ID: 9852753
Bulzeye --
 Your solution looks like it would work well... however when I tried to run it on my table I got the following error:

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

LowFatSpread --
Your new solution worked! :)  However, the results came back as follows:

ACCOUNT_OWNERSHIP_DATATXT
ACCOUNT_TYPETXT

I can always run another replace function to get rid of the TXT ... but is there a way within your code to have it take out the TXT as well?
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 9852802
yes sorry forgot to strip the txt from the rightbit...

-- X  identify rows with *.Txt files....
-- Y  Reverse the left hand side of the string
--      so that  abc_500.txt   is now 005_cba
-- Z  confirm that a _ precedes the numeric bit...
-- U  confirm the first part of the reversed string is numeric
--
-- The update the col by reversing the reversed leftbit minus the numeric
--   and add back on any trailing characters after the .txt
--
-- hey presto!
 

UPDATE #TempTableNames
SET Col001 = rEVERSE(SUBSTRING(LEFTBIT,NUMSTART+1,DATALENGTH(LEFTBIT) - nUMSTART))
              + rIGHTBIT
FROM #TempTableNames
INNER JOIN
       ( Select z.*
         from (
                Select col001,Charindex('_',leftbit) as numstart,rightbit,constpos,leftbit
                  From (
                         Select col001,reverse(left(col001,constpos - 1 )) as leftbit
                               ,constpos
                               , substr(col001,constpos + 5,Datalength(col001) - constpos - 4)
                                 as rightbit
                           From  
                                (SELECT Col001,
                                     charINDEX('.txt', Col001) As ConstPos
                                   FROM #TempTableNames
                                   WHERE charINDEX('.txt', Col001) > 0)
                                 ) AS x  
                       ) as Y  
                  Where charindex('_',leftbit) > 0
              ) as Z
          Where Isnumeric(left(leftbit,numstart-1)) = 1
       ) AS u
ON #TempTableNames.Col001 = U.Col001


0
 

Author Comment

by:Roxanne25
ID: 9852838
Hmmm... I just ran it again and the TXT is still there... O.o
0
 

Author Comment

by:Roxanne25
ID: 9852854
Hehe, nevermind... silly me... I forgot to truncate the table first! :)  It worked!


TY TY TY TY TY TY so much!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now