CREATE TABLE capital ([InternalValue] VARCHAR(255), [ExternalValue] VARCHAR(255));

CREATE TABLE instrument ([Instrument] VARCHAR(255));

INSERT INTO capital ([InternalValue], [ExternalValue])
VALUES
('Usd', 'USD'),
('Ltd', 'LTD'),
('Corp', 'CORP'),
('Inc', 'INC')

INSERT INTO instrument ([Instrument])
VALUES
('Merck & Co Inc Common Stock Usd.5'),
('Newmont Mining Corp Common Stock Usd Inc 1.6');

CREATE FUNCTION [dbo].[fn_replacementsecurityname_test] (@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN

    WITH ReplaceWord(InternalWord, ExternalWord) AS
    (
        SELECT InternalValue, ExternalValue
        FROM capital
    )
    SELECT @string =  REPLACE(@string, r.InternalWord, r.ExternalWord)
    FROM ReplaceWord r
    WHERE CHARINDEX(r.InternalWord, @string) > 0

    RETURN @string
END

SELECT dbo.replacement(i.Instrument) NewValue
FROM instrument AS i

Just run this script once
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
         DROP TABLE #mytable
     IF OBJECT_ID('TempDB..#replacements','U') IS NOT NULL
         DROP TABLE #replacements

--===== Create the test table with 
 CREATE TABLE #mytable 
        (
        ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        LongString nvarchar(max)
        )

--===== Create table with replacements
CREATE TABLE #replacements
(
  code nvarchar(55)
 ,replacement nvarchar(255)
)


--===== Add sample data
INSERT INTO #mytable (LongString)
select 'code01 something else code02' union all
select 'code02 code02 aacode01'

INSERT INTO #replacements
select 'code01', 'XXXX' union all
select 'code02', 'YYYY'


-- solution
;WITH Tally AS (SELECT top 100 n = ROW_NUMBER() OVER(ORDER BY [name]) from master.dbo.syscolumns),
NormalisedData AS (
	SELECT m.ID,
		WordID = ROW_NUMBER() OVER(PARTITION BY m.ID ORDER BY t.n), 
		m.longstring, 
		word = SUBSTRING(m.longstring, t.n,
		(ISNULL(NULLIF(CHARINDEX(' ', SUBSTRING(m.longstring, t.n, LEN(m.LongString)-t.n)), 0), 
		LEN(SUBSTRING(m.longstring, t.n, LEN(m.LongString)-t.n))+2)) - 1)
	FROM #mytable m
	INNER JOIN Tally t ON SUBSTRING(' ' + m.LongString, t.n, 1) = ' '
	WHERE t.n <= LEN(m.LongString) 
),
ReplacedData AS (
	SELECT d.ID, d.WordID, d.LongString, NewWord = ISNULL(r.replacement, d.word)
	FROM NormalisedData d 
	LEFT JOIN #replacements r ON r.code = d.word
)
SELECT d.ID, OldString = d.LongString,
	NewString = (
		SELECT ' ' + NewWord  
		FROM ReplacedData r
		WHERE r.ID = d.ID
		ORDER BY ID, WordID
		FOR XML PATH(''))
FROM ReplacedData d 
GROUP BY d.ID, d.LongString 

IF OBJECT_ID('mytable','U') IS NOT NULL
         DROP TABLE mytable
     IF OBJECT_ID('replacements','U') IS NOT NULL
         DROP TABLE replacements

--===== Create the test table with 
 CREATE TABLE mytable 
        (
        ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        LongString nvarchar(max)
        )

--===== Create table with replacements
CREATE TABLE replacements
(
  code nvarchar(55)
 ,replacement nvarchar(255)
)


--===== Add sample data
INSERT INTO mytable (LongString)
select 'code01 something else code02' union all
select 'code02 code02 aacode01'

INSERT INTO replacements
select 'code01', 'XXXX' union all
select 'code02', 'YYYY'
GO
CREATE FUNCTION MyOwnReplace (@str varchar(1000))
RETURNs VARCHAR(2000)
AS
BEGIN
DECLARE @NewString varchar(2000) = ''
;WITH Tally AS 
(SELECT top 100 n = ROW_NUMBER() OVER(ORDER BY [name]) from master.dbo.syscolumns)
,NormalisedData AS (
	SELECT m.ID,
		WordID = ROW_NUMBER() OVER(PARTITION BY m.ID ORDER BY t.n), 
		m.longstring, 
		word = SUBSTRING(m.longstring, t.n,
		(ISNULL(NULLIF(CHARINDEX(' ', SUBSTRING(m.longstring, t.n, LEN(m.LongString)-t.n)), 0), 
		LEN(SUBSTRING(m.longstring, t.n, LEN(m.LongString)-t.n))+2)) - 1)
	FROM mytable m 
	INNER JOIN Tally t ON SUBSTRING(' ' + m.LongString, t.n, 1) = ' ' AND m.LongString = @str
	WHERE t.n <= LEN(m.LongString) 
),
ReplacedData AS (
	SELECT d.ID, d.WordID, d.LongString, NewWord = ISNULL(r.replacement, d.word)
	FROM NormalisedData d 
	LEFT JOIN replacements r ON r.code = d.word
)
SELECT @NewString = (
		SELECT ' ' + NewWord  
		FROM ReplacedData r
		WHERE r.ID = d.ID
		ORDER BY ID, WordID
		FOR XML PATH(''))
FROM ReplacedData d 
GROUP BY d.ID, d.LongString 
RETURN @NewString
END
--Testing
SELECT dbo.MyOwnReplace(LongString), LongString
FROM mytable

quote:

---

Originally posted by chrissyppppp

Thanks for the response. Is there anyway you could convert this into a function? This is what i'm truely after so that i can supply it with a string and it do a lookup to be able to replace the words that need replacing from the cross reference table.

Thanks

---

quote:

---

Originally posted by chrissyppppp

I simply had the temporary table above as an example. I would prefer to use a function due to performance

---

quote:

---

So this works fine, however how can I ensure that i'm replacing what i want to replace. ie in the above example say we wanted all "Co" to be "CO" but also ensuring that "Common" didn't become "COmmon"? Is there any way we could look at the length of the replacement string and that it only replaces words of that length?

---

quote:

---

Originally posted by bandi

quote:

---

So this works fine, however how can I ensure that i'm replacing what i want to replace. ie in the above example say we wanted all "Co" to be "CO" but also ensuring that "Common" didn't become "COmmon"? Is there any way we could look at the length of the replacement string and that it only replaces words of that length?

---

Hi simple solution for above problem is:
In your first function, do this modification

SELECT @string = REPLACE(@string, ' '+ r.InternalWord + ' ', ' '+r.ExternalWord+ ' ')
FROM ReplaceWord

--
Chandu

---

quote:

---

Originally posted by visakh16

case difference would cause search issues only when database collation used is case sensitive. By default sql server collation is case insensitive so case difference doesnt make any issues.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---