August 12, 2025 at 10:49 pm
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
August 12, 2025 at 11:08 pm
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
August 13, 2025 at 6:47 am
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
August 21, 2025 at 9:44 am
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
August 21, 2025 at 9:03 pm
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