Replace with like join

  • I am trying to create and Update query based on a like join.  Is that possible?  This is what I have:

    UPDATE claim

    SET incident_desc =

    CASE WHEN incident_desc like '%' + NAC.Glyph + '%' THEN REPLACE(claim.incident_desc, NAC.Glyph, NAC.Replacement)

    ELSE incident_desc

    END

    FROM claim INNER JOIN

    NonASCIICharacters  NAC ON claim.incident_desc LIKE '%' + NAC.Glyph + '%'

    Glyph = ª with and underscore

    So trying to replace the ª in ''LUMBAR - FUT MEDSª, INJURED LOWER BACK LIFTING DRUG BOX' with a space.  When I run the update it just totally deletes the comment.  Any help would be appreciated.

    Thanks,

    Jimmy

     

     

     

     

    • This topic was modified 3 weeks, 2 days ago by James Bosco.
  • Here is an example of what I am trying to do:

     

     

    HEAD-FUT MEDSª, ALLEGES TENSION HEADACHES DUE TO STRESS OF THE JOB

    HEAD-FUT MEDS, ALLEGES TENSION HEADACHES DUE TO STRESS OF THE JOB

     

  • Maybe this can get you towards your quest

    Declare @string varchar(160) = 'LUMBAR - FUT MEDSª, INJURED LOWER BACK LIFTING DRUG BOX'
    , @position int = 1

    Select len(@string) len_String, DATALENGTH(@string) DATALENGTH_String


    Declare @tb table ( Position int not null primary key, Char1 char(1) not null, [ASCII_val] int not null, [ASCII_2_String] char(1) not null );

    set nocount on;
    WHILE @position < DATALENGTH(@string) + 1
    BEGIN
    insert into @tb ( Position, Char1, [ASCII_val], [ASCII_2_String] )
    SELECT @position
    , SUBSTRING(@string, @position, 1) AS [Char1]
    , ASCII(SUBSTRING(@string, @position, 1)) AS [ASCII_val]
    , CHAR(ASCII(SUBSTRING(@string, @position, 1))) AS [ASCII2String];

    select @position = @position + 1;

    END

    set nocount off;

    Select *
    from @tb
    order by ASCII_val desc ,Position

    Select @string as StartString, replace( @string, Char1, '') newstring
    from @tb
    where ASCII_val > 88

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • First, create a table-valued function that can be re-used elsewhere such as the one below which I originally found somewhere on this site. Then use the function to remove any characters other than those you want to allow; the example below removes all non-alphanumeric characters apart from comma, full-stop, space and hyphen.

    UPDATE UpdRec

    SET incident_desc = upe.NewString

    FROM claim UpdRec

    CROSS APPLY dbo.ufn_PatternExclude(incident_desc, '^[0-9A-Za-z],._-') upe

     

    CREATE FUNCTION [dbo].[ufn_PatternExclude]
    (
    @String VARCHAR(8000),
    @Pattern VARCHAR(50)
    )
    /*******************************************************************************
    Purpose:
    Given a string (@String) and a pattern (@Pattern) of characters to remove,
    remove the patterned characters from the string.

    Usage:
    --===== Basic Syntax Example
    SELECT NewString
    FROM dbo.ufn_PatternExclude(@String,@Pattern);

    --===== Remove all but Alpha characters
    SELECT NewString
    FROM dbo.SomeTable st
    CROSS APPLY dbo.ufn_PatternExclude(st.SomeString,'[^A-Za-z]');

    --===== Remove all but Numeric digits
    SELECT NewString
    FROM dbo.SomeTable st
    CROSS APPLY dbo.ufn_PatternExclude(st.SomeString,'[^0-9]');

    Programmer Notes:
    1. @Pattern is not case sensitive (the function can be easily modified to make it so)
    2. There is no need to include the "%" before and/or after your pattern since since we
    are evaluating each character individually

    Revision History:
    Rev 00 - 10/27/2014 Initial Development - Alan Burstein

    Rev 01 - 10/29/2014 Mar 2007 - Alan Burstein
    - Redesigned based on the dbo.STRIP_NUM_EE by Eirikur Eiriksson
    (see: http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx)
    - change how the cte tally table is created
    - put the include/exclude logic in a CASE statement instead of a WHERE clause
    - Added Latin1_General_BIN Colation
    - Add code to use the pattern as a parameter.

    Rev 02 - 11/6/2014
    - Added final performance enhancement (more kudos to Eirikur Eiriksson)
    - Put 0 = PATINDEX filter logic into the WHERE clause

    Rev 03 - 5/16/2015
    - Updated code to deal with special XML characters

    Rev 04 - 25 Oct 2017 - Chris Wooding
    - Removed redundant conversion of LEN to int.
    - Set @String to '' instead of null before checking length for TOP clause.

    *******************************************************************************/
    RETURNS TABLE
    WITH SCHEMABINDING
    AS
    RETURN
    WITHE1(N)
    AS (SELECT N
    FROM ( VALUES ( NULL), ( NULL), ( NULL), ( NULL), ( NULL),
    ( NULL), ( NULL), ( NULL), ( NULL), ( NULL) ) AS X (N)),
    itally(N)
    AS (SELECT TOP (LEN(ISNULL(@String, ''))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM E1 T1
    CROSS JOIN E1 T2
    CROSS JOIN E1 T3
    CROSS JOIN E1 T4)
    SELECT NewString = ((
    SELECT SUBSTRING(@String, N, 1)
    FROM itally
    WHERE 0 = PATINDEX(@Pattern,
    SUBSTRING(@String COLLATE Latin1_General_BIN, N, 1))
    FORXML PATH(''),
    TYPE
    ).value('.[1]', 'varchar(8000)'));

    GO
  • Maybe I'm a bit slow here, but is there a reason you need all of that?

    With REPLACE, if the character doesn't exist in the string, it should return the string, no?

    If you have only a few characters to replace (either of the tables is small) do a cross join (join on 1=1). If there are 5 rows in claims and 10 in NonAsciiCharacters (for example) you will get 50 rows back. So you can see why I'd only do that if you have a small number of rows to work with.

    I would also start with a SELECT statement so I can see how much data I am working with and if my SELECT query makes sense. After that, work on the UPDATE.

    So what I am suggesting is something like this:

    //UPDATE claim
    //SET incident_desc =
    SELECT claim.incident_desc,
    REPLACE(claim.incident_desc, NAC.Glyph, NAC.Replacement)
    FROM claim INNER JOIN
    NonASCIICharacters NAC ON 1=1 // Cross join
    // OPTIONAL WHERE
    // WHERE cliam.indcident_desc like N'%' + NAC.Glyph + '%'

    SELECT first to make sure things work how I expect, then comment out the SELECT line and uncomment the UPDATE and SET.

    Now, if it is a lot of data, then a cross join is likely a bad option and the other suggestions may be better OR you may want to filter the data in the WHERE clause using my query. The reason I like my approach though is that REPLACE returns the original string if nothing is replaced.

    And a tip for working with UPDATE queries - START with a SELECT to make sure the query is doing what you want, the change the SELECT part to an UPDATE like how I showed in the above query.

    Second note - I think my query will fail with SOME special characters like emoji's. I did not test it, but I did test with the 1 sample you provided and it worked on my machine (SQL Server 2017).

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply