Solved

Replace Function With Wildcards

Posted on 2003-11-25
20
1,129 Views
Last Modified: 2012-05-04
Hello again!  More SQL Server questions abound!  I am using the Replace function on a table that holds about 600 Create Table statements that are written in PL/SQL.  I am using Replace to parse down the data to a usable create table statement by SQL Server.  The process is working quite nicely however, I am trying to think about using this procedure when the table has new entries and then has some different string sets that need to be removed.  

One example that I need to remove are all the CONSTRAINT expressions... there are a lot of different constraints, and I would like the ability to say:
Replace(Column1,like 'Constraint%,','')
Where it would replace everything that lied between the word Constraint and a comma.  

I know Replace functino doesn't work this way... but is there a way within Replace to do a select statement or some kind of clause or is there another way all together to accomplish this?  Or am I gonna have to list out every single constraint one by one? :)

Thanks!
0
Comment
Question by:Roxanne25
  • 11
  • 9
20 Comments
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
@Roxanne25:

> I know Replace functino doesn't work this way... but is there a way within
> Replace to do a select statement or some kind of clause or is there another
> way all together to accomplish this?  Or am I gonna have to list out every
> single constraint one by one? :)

Try this:
      SELECT
            ID, Column1,
            REPLACE( Column1, SUBSTRING(Column1, ConstPos, CHARINDEX(Column1, ',', ConstPos) - ConstPos), '' ) AS FixedColumn1
      FROM
            (SELECT ID, Column1, PATINDEX('CONSTRAINT%,', Column1) As ConstPos FROM YourTable
            WHERE PATINDEX('CONSTRAINT%,', Column1) > 0) AS ConstTable

That should return 3 columns, the ID, Your original Column1, and the "fixed" column1.  Let me know if the Fixed column looks like how you'd want the updated column to look.  If it is, then we can change this statement into an update one fairly easily.

Hope That Helps,
Dex*
0
 

Author Comment

by:Roxanne25
Comment Utility
Hi there, thank you for your reply... I modified the query you gave me to reflect my data, and when I ran it, it came back blank.

SELECT
          Col001,
          REPLACE( Col001, SUBSTRING(Col001, ConstPos, CHARINDEX(Col001, ',', ConstPos) - ConstPos), '' ) AS FixedColumn1
     FROM
          (SELECT Col001, PATINDEX('CONSTRAINT%,', Col001) As ConstPos FROM [Columns]
          WHERE PATINDEX('CONSTRAINT%,', Col001) > 0) AS ConstTable

I'm wondering if this has to do with the fact that there are multiple occurrences of Constraints in one row of the table?  Or did you take that into consideration?  Here is an example of one row in my table:

CREATE TABLE ACCOUNT_TYPE (ACC_TYPE VARCHAR(1) , ACC_SUB_TYPE VARCHAR(1)  , SYS_CREATION_DATETIME DATETIME , SYS_UPDATETIME_DATETIME DATETIME, OPERATOR_ID NUMBER(9, 0), APPLICATION_ID VARCHAR(6), DL_SERVICE_CODE VARCHAR(5) , DL_UPDATETIME_STAMP NUMBER(4, 0), SUBS_NOT_ALLOWED VARCHAR(1)  , NAME_FORMAT VARCHAR(1), ADDRESS_REQ_IND VARCHAR(1), CAS_CREDIT_REQ_IND VARCHAR(1), INITIAL_CREDIT_CLASS VARCHAR(1), DEF_CAS_APPL_TYPE VARCHAR(6), FORCE_ZERO_BAL_IND VARCHAR(1), BILL_PROD_IND VARCHAR(1), BALANCE_HANDLE_IND VARCHAR(1), CCIR_PROD_IND VARCHAR(1), DEF_APPR_CTN_QTY NUMBER(4, 0), NATIONAL_NO_REQ_IND VARCHAR(1), DESCRIPTION VARVARCHAR(40), SPECIAL_PARAMS VARVARCHAR(1000), DEF_PRICE_CD_EQP VARCHAR(6), DEF_PRICE_CD_SRV VARCHAR(6), UNIDENTIFIED_PYM_IND VARCHAR(1), CUST_TP_FOR_TAX VARCHAR(1), WELCOME_LETTER VARCHAR(1), ALLOW_CHANGE_TYPE VARCHAR(1) CONSTRAINT ACCTP_ALLOW_CHANGE_TYPE_NN , SOC_MATCH VARCHAR(1), RA_IND VARCHAR(1), DEFAULT_IND VARCHAR(1), DEF_SUPPRESS_BILL VARCHAR(1), ERATE_IND VARCHAR(1), SPEC_RES_ALLOC_IND VARCHAR(1) CONSTRAINT ACCTP_SPEC_RES_ALLOC_IND_NN , LTPYMCRG_ALLOW_IND VARCHAR(1), GSA_GRID_PRST_IND VARCHAR(1), CORP_ID_IND VARCHAR(1) CONSTRAINT ACCTP_CORP_ID_IND_NN , AUTO_VARCHARGE_IND VARCHAR(1) CONSTRAINT ACCTP_AUTO_VARCHARGE_IND_NN , PREPAY_IND VARCHAR(1) CONSTRAINT ACCTP_PREPAY_IND_NN , RESTRICT_EQUIP_IND VARCHAR(1) CONSTRAINT ACCTP_RESTRICT_EQUIP_IND_NN , CRD_LMT_IND VARCHAR(1) CONSTRAINT ACCTP_CRD_LMT_IND_NN , IDENTIFY_REQ_IND VARCHAR(1) CONSTRAINT ACCTP_IDENTIFY_REQ_IND_NN , SSN_TAX_ID_REQ VARCHAR(1) CONSTRAINT ACCTP_SSN_TAX_ID_REQ_NN , CNAM_SE VARVARCHAR(3) CONSTRAINT ACCTP_CNAM_SE_NN , SSN_IND VARCHAR(1) CONSTRAINT ACCTP_SSN_IND_NN , TAX_ID_IND VARCHAR(1) CONSTRAINT ACCTP_TAX_ID_IND_NN , PS_IND VARCHAR(1)  , DVC_TP VARVARCHAR(30) CONSTRAINT ACCTP_DVC_TP_NN )  
0
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
No, I had some issues with my parameters to PATINDEX and CHARINDEX...  Try this:

     SELECT
        Col001,
        REPLACE( Col001, SUBSTRING(Col001, ConstPos, CHARINDEX(',', Col001, ConstPos) - ConstPos + 1), '' ) AS FixedColumn1
     FROM
          (SELECT Col001, PATINDEX('%CONSTRAINT%,%', Col001) As ConstPos FROM [Columns]
          WHERE PATINDEX('%CONSTRAINT%,%', Col001) > 0) AS ConstTable

That will only replace the first occurrance, but once we have the update query going, you can keep running it until there are no more occurances.  Simple enough.

Dex*
0
 

Author Comment

by:Roxanne25
Comment Utility
Wow, ok...it worked well I think... except that I don't want it to take out the comma.  It needs to find what lies between CONSTRAINT and , but take out everything except the comma.  Does that make sense?

Other than that it looks perfect!  
0
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
Oh, I thought you wanted the comma removed.  In that case, just get rid of the +1 in the Replace statement.  Try this:

     SELECT
        Col001,
        REPLACE( Col001, SUBSTRING(Col001, ConstPos, CHARINDEX(',', Col001, ConstPos) - ConstPos), '' ) AS FixedColumn1
     FROM
          (SELECT Col001, PATINDEX('%CONSTRAINT%,%', Col001) As ConstPos FROM [Columns]
          WHERE PATINDEX('%CONSTRAINT%,%', Col001) > 0) AS ConstTable

Dex*
0
 

Author Comment

by:Roxanne25
Comment Utility
Yuppers, that looks exactly the way I need it to be.  So, how do I go about implementing it to do the update over and over till it doesn't find any occurrences anymore?

And btw, THANK YOU so much... just saved my butt. :)
0
 
LVL 19

Accepted Solution

by:
Dexstar earned 500 total points
Comment Utility
You're welcome...  Here is the statement you'll need:

WHILE EXISTS( SELECT * FROM [Columns] WHERE PATINDEX('%CONSTRAINT%,%', Col001) > 0 )
UPDATE [Columns]
SET Col001 = FixedColumn1
FROM [Columns] INNER JOIN
     (SELECT
        Col001,
        REPLACE( Col001, SUBSTRING(Col001, ConstPos, CHARINDEX(',', Col001, ConstPos) - ConstPos), '' ) AS FixedColumn1
     FROM
          (SELECT Col001, PATINDEX('%CONSTRAINT%,%', Col001) As ConstPos FROM [Columns]
          WHERE PATINDEX('%CONSTRAINT%,%', Col001) > 0) AS ConstTable) AS UpdatedColumns
ON [Columns].Col001 = UpdatedColumns.Col001
0
 

Author Comment

by:Roxanne25
Comment Utility
Woo..... ty ty ty.... works great! :)
0
 

Author Comment

by:Roxanne25
Comment Utility
Oh, one more question... if I wanted to do this for other strings would I just modifiy the PatIndex part to whatever string I wanted it to be from?  Or does that mess up the rest of the code?
0
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
You're welcome!  :)  Thank you for the question!

Dex*
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 19

Expert Comment

by:Dexstar
Comment Utility
@Roxanne25:

Yeah, basically.  Just be sure you change all the PATINDEXes the same way.

PATINDEX('%CONSTRAINT%,%', Col001) searches for anything with CONSTRAINT and then a comma later.  If you want to change the comma to a different character, you'll also have to change the CHARINDEX part (notice the first parameter is a comma).

Hope That Helps,
Dex*
0
 

Author Comment

by:Roxanne25
Comment Utility
Hmm... ok, is the CharIndex part that tells it where to stop?  I suppose I'm confused on what exactly the code is doing as I have never used any of these functions before.  For example, I think I might want to do it as well where I would change the PatIndex to ('PCTFREE%') meaning that I want it to delete PCTFREE and anything that comes after it.... so I would change the CharIndex to ''?  I think if I can figure that out I can modify it myself in the future... again thanks!
0
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
Yeah, the CHARINDEX tells it where to stop.  The SUBSTRING gets the substring between PATINDEX and CHARINDEX and then the REPLACE function replaces that string.  It isn't very complicated.

In your other example, where you want to delete everything after it, then you would want to change the query slightly, and not user CHARINDEX at all.  The replace line would look like this:

     REPLACE( Col001, SUBSTRING(Col001, ConstPos, '' ) AS FixedColumn1

Which means you want the substring that starts at ConstPos, and goes to the end of the string.

Enjoy.
Dex*
0
 

Author Comment

by:Roxanne25
Comment Utility
Oh hmmm... I tried substituting what you gave me and it errored out saying that replace function requires 3 arguments...

WHILE EXISTS( SELECT * FROM [Columns] WHERE PATINDEX('PCTFREE%', Col001) > 0 )
UPDATE [Columns]
SET Col001 = FixedColumn1
FROM [Columns] INNER JOIN
     (SELECT
        Col001,
         REPLACE( Col001, SUBSTRING(Col001, ConstPos, '') ) AS FixedColumn1
     FROM
          (SELECT Col001, PATINDEX('PCTFREE%', Col001) As ConstPos FROM [Columns]
          WHERE PATINDEX('PCTFREE%', Col001) > 0) AS ConstTable) AS UpdatedColumns
ON [Columns].Col001 = UpdatedColumns.Col001

Sorry ... I can open another question and give you some more points if you would like...
0
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
Oops... I forgot a parenthesis...
    REPLACE( Col001, SUBSTRING(Col001, ConstPos, '' ) AS FixedColumn1

Should be this instead:
    REPLACE( Col001, SUBSTRING(Col001, ConstPos), '' ) AS FixedColumn1

There we go.




0
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
You might also need to use PATINDEX('%PCTFREE%', Col001) instead of PATINDEX('PCTFREE%', Col001).

The difference is that former will find PCTFREE anywhere it appears in a string and the later will only find the ones that occur at the beginning of a string.

Dex*
0
 

Author Comment

by:Roxanne25
Comment Utility
Oh dear, what did I do wrong?  I am getting this error:
Server: Msg 174, Level 15, State 1, Line 7
The substring function requires 3 arguments.
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'AS'.

It seems to be correct:
WHILE EXISTS( SELECT * FROM [Columns] WHERE PATINDEX('%PCTFREE%', Col001) > 0 )
UPDATE [Columns]
SET Col001 = FixedColumn1
FROM [Columns] INNER JOIN
     (SELECT
        Col001,
         REPLACE( Col001, SUBSTRING(Col001, ConstPos), '' ) AS FixedColumn1
     FROM
          (SELECT Col001, PATINDEX('%PCTFREE%', Col001) As ConstPos FROM [Columns]
          WHERE PATINDEX('%PCTFREE%', Col001) > 0) AS ConstTable) AS UpdatedColumns
ON [Columns].Col001 = UpdatedColumns.Col001
0
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
Oops.  I told you wrong.  You can't call SUBSTRING with just 2 parameters.  You have to use 3.  So, I just added a 3rd one that will compute the remainder of the string, whichi is what you want.  Try this:

WHILE EXISTS( SELECT * FROM [Columns] WHERE PATINDEX('%PCTFREE%', Col001) > 0 )
UPDATE [Columns]
SET Col001 = FixedColumn1
FROM [Columns] INNER JOIN
     (SELECT
        Col001,
         REPLACE( Col001, SUBSTRING(Col001, ConstPos, LEN(Col001)-ConstPos), '' ) AS FixedColumn1
     FROM
          (SELECT Col001, PATINDEX('%PCTFREE%', Col001) As ConstPos FROM [Columns]
          WHERE PATINDEX('%PCTFREE%', Col001) > 0) AS ConstTable) AS UpdatedColumns
ON [Columns].Col001 = UpdatedColumns.Col001
0
 

Author Comment

by:Roxanne25
Comment Utility
Hehe, ok ... that worked... thanks a bunch.  This process would have been almost impossible without your help!  Thanks!
0
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
You're welcome!  :)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

13 Experts available now in Live!

Get 1:1 Help Now