Removing Unwanted Text From Column

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
Roxanne25Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
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
Roxanne25Author Commented:
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
LowfatspreadCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Roxanne25Author Commented:
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
LowfatspreadCommented:
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
LowfatspreadCommented:
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
Roxanne25Author Commented:
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
LowfatspreadCommented:
Where Isnumeric(leftbit,1,numstart-1) = 1

should be

Where Isnumeric(Left(leftbit,numstart-1)) = 1
       
       
0
Roxanne25Author Commented:
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
LowfatspreadCommented:
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
Roxanne25Author Commented:
Good news... :)  There are no more errors in the code... bad news is:

0 Rows Affected.

Not sure what to do now....
0
LowfatspreadCommented:
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
Roxanne25Author Commented:
I ran those as you suggested, and it didn't return any rows at all.
0
LowfatspreadCommented:
post the results of this please
SELECT top 2 Col001,  PATINDEX('%.txt', Col001) As ConstPos
                                   FROM #TempTableNames
                                   
0
Roxanne25Author Commented:
ACCOUNT_OWNERSHIP_DATA_1.TXT      25
ACCOUNT_TYPE_2.TXT      15
0
LowfatspreadCommented:
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
LowfatspreadCommented:
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
BulZeyECommented:
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
Roxanne25Author Commented:
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
LowfatspreadCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roxanne25Author Commented:
Hmmm... I just ran it again and the TXT is still there... O.o
0
Roxanne25Author Commented:
Hehe, nevermind... silly me... I forgot to truncate the table first! :)  It worked!


TY TY TY TY TY TY so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.