Is there a better faster way then REPLACE in this example?

• ghughes

  SSCrazy

  Points: 2201

  More actions

  September 21, 2016 at 8:16 am

  #317723

  I am wondering if there is a better way............. here was my challenge.

  I have 2 tables and I am attempting to match models. (they are described differently in both tables).

  IPHONE 6S ROSE 64GB SGL vs IPHONE 6S ROSE 64GB KIT

  IPHONE 6S ROSE 64GB SGL vs IPHONE 6S ROSE 16GB KIT

  SAMSUNG L900 SILVER vs SAMSUNG N930P BLK KIT

  SAM G935 32GB BK XCVR SGL vs SAMSUNG G935P 32GB BK KIT

  IPH 6S PLUS GRAY 64GB SGL vs IPHONE 6S PLUS GRAY 64GB KIT

  so I created a function to remove non relevant words (see below)

  -- User Defined Function to strip out none relevent words

  -- the result is on the model and gigabytes

  CREATE FUNCTION [dbo].[udf_Model_Name_Compare]

  (@ModName VARCHAR(100))

  RETURNS VARCHAR(100)

  AS

  BEGIN

  -- it is highly recommended words be stripped out in this order

  --special words

  SET @ModName = (SELECT REPLACE(@ModName,' SNGL',''))

  SET @ModName = (SELECT REPLACE(@ModName,' KIT',''))

  SET @ModName = (SELECT REPLACE(@ModName,' KT',''))

  SET @ModName = (SELECT REPLACE(@ModName,' KI',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SGL',''))

  SET @ModName = (SELECT REPLACE(@ModName,' XSGL',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SNG',''))

  SET @ModName = (SELECT REPLACE(@ModName,' XCVR',''))

  SET @ModName = (SELECT REPLACE(@ModName,' HOTSPOT',''))

  SET @ModName = (SELECT REPLACE(@ModName,' HTSPT',''))

  SET @ModName = (SELECT REPLACE(@ModName,' TRANSCEIVER',''))

  SET @ModName = (SELECT REPLACE(@ModName,' HANDSET',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SG',''))

  SET @ModName = (SELECT REPLACE(@ModName,' DEVICE',''))

  SET @ModName = (SELECT REPLACE(@ModName,' DVC',''))

  SET @ModName = (SELECT REPLACE(@ModName,' LTE',''))

  SET @ModName = (SELECT REPLACE(@ModName,' TABLET',''))

  SET @ModName = (SELECT REPLACE(@ModName,'CPO ',''))

  --manufactors

  SET @ModName = (SELECT REPLACE(@ModName,'IPHONE ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'HTC ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'LG ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'IPH ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'SAM ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'SAMSUNG ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'PALM ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'SANYO ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'NETGEAR ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'KYOCERA ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'KYO ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'FRANKLIN ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'ALCATEL ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'MOTO ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'ZTE ',''))

  ----colors last

  SET @ModName = (SELECT REPLACE(@ModName,'WHITE ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' WHITE',''))

  SET @ModName = (SELECT REPLACE(@ModName,'WHT ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' WHT',''))

  SET @ModName = (SELECT REPLACE(@ModName,'WH ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' WH',''))

  SET @ModName = (SELECT REPLACE(@ModName,'PINK ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'GOLD ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'GOLD ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' GOLD',''))

  SET @ModName = (SELECT REPLACE(@ModName,'GLD ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' GLD',''))

  SET @ModName = (SELECT REPLACE(@ModName,' GD',''))

  SET @ModName = (SELECT REPLACE(@ModName,'BLK ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' BLK',''))

  SET @ModName = (SELECT REPLACE(@ModName,'BLACK ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' BLACK',''))

  SET @ModName = (SELECT REPLACE(@ModName,' BK',' '))

  SET @ModName = (SELECT REPLACE(@ModName,' BK ',' '))

  SET @ModName = (SELECT REPLACE(@ModName,'GREEN ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' GREEN',''))

  SET @ModName = (SELECT REPLACE(@ModName,'GRAY ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' GRAY',''))

  SET @ModName = (SELECT REPLACE(@ModName,'GRY ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' GRY',''))

  SET @ModName = (SELECT REPLACE(@ModName,'SILVER ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SILVER',''))

  SET @ModName = (SELECT REPLACE(@ModName,'SILV ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SILV',''))

  SET @ModName = (SELECT REPLACE(@ModName,'SLV ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SLV',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SV',''))

  SET @ModName = (SELECT REPLACE(@ModName,'ROSE ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' ROSE',''))

  SET @ModName = (SELECT REPLACE(@ModName,'BLUE ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' BLUE',''))

  SET @ModName = (SELECT REPLACE(@ModName,'BLU ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' BLU',''))

  SET @ModName = (SELECT REPLACE(@ModName,'RED ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' RED',''))

  SET @ModName = (SELECT REPLACE(@ModName,'NAVY ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' NAVY',''))

  SET @ModName = (SELECT REPLACE(@ModName,' ',' '))

  SET @ModName = (SELECT REPLACE(@ModName,' ',' '))

  SET @ModName = (SELECT LTRIM(RTRIM(@ModName)))

  RETURN @ModName

  END

  ---------------------------------------

  So then I use a case statement (see snipnet below)

  WHEN xxx.dbo.udf_Model_Name_Compare(l.MODEL_NAME) = xxx.dbo.udf_Model_Name_Compare(l.LOAN_MODEL_NAME) THEN 'Yes'

  ELSE 'Needs manual review of model match' END

  The question is ……….. is there a better way? This is used for every record. and sometimes that is in the 1,000+ records at a time.

  The function works very well, I am just wondering if there is a better way?

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  September 21, 2016 at 9:00 am

  #1902205

  1) You can probably save some CPU ticks if you nest REPLACEs up to whatever level they can be nested instead of doing that many separate SET statements. This savings if available may not be enough to see on small sets of data.

  2) Given the need for order in the replacement, I don't know if you can come up with a spiffy way to do this logic or not.

  3) If you have to do this more than once I would consider creating a column in which you store the post-"compare" logic string so you don't have to burn that Scalar UDF over and over. Those things are bad! I would make it an actual column too, not a computed/persisted one. Depending on other factors indexing this could be useful as well.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  September 21, 2016 at 9:03 am

  #1902209

  ghughes (9/21/2016)

  ---

  I am wondering if there is a better way............. here was my challenge.

  I have 2 tables and I am attempting to match models. (they are described differently in both tables).

  IPHONE 6S ROSE 64GB SGL vs IPHONE 6S ROSE 64GB KIT

  IPHONE 6S ROSE 64GB SGL vs IPHONE 6S ROSE 16GB KIT

  SAMSUNG L900 SILVER vs SAMSUNG N930P BLK KIT

  SAM G935 32GB BK XCVR SGL vs SAMSUNG G935P 32GB BK KIT

  IPH 6S PLUS GRAY 64GB SGL vs IPHONE 6S PLUS GRAY 64GB KIT

  so I created a function to remove non relevant words (see below)

  -- User Defined Function to strip out none relevent words

  -- the result is on the model and gigabytes

  CREATE FUNCTION [dbo].[udf_Model_Name_Compare]

  (@ModName VARCHAR(100))

  RETURNS VARCHAR(100)

  AS

  BEGIN

  -- it is highly recommended words be stripped out in this order

  --special words

  SET @ModName = (SELECT REPLACE(@ModName,' SNGL',''))

  SET @ModName = (SELECT REPLACE(@ModName,' KIT',''))

  SET @ModName = (SELECT REPLACE(@ModName,' KT',''))

  SET @ModName = (SELECT REPLACE(@ModName,' KI',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SGL',''))

  SET @ModName = (SELECT REPLACE(@ModName,' XSGL',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SNG',''))

  SET @ModName = (SELECT REPLACE(@ModName,' XCVR',''))

  SET @ModName = (SELECT REPLACE(@ModName,' HOTSPOT',''))

  SET @ModName = (SELECT REPLACE(@ModName,' HTSPT',''))

  SET @ModName = (SELECT REPLACE(@ModName,' TRANSCEIVER',''))

  SET @ModName = (SELECT REPLACE(@ModName,' HANDSET',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SG',''))

  SET @ModName = (SELECT REPLACE(@ModName,' DEVICE',''))

  SET @ModName = (SELECT REPLACE(@ModName,' DVC',''))

  SET @ModName = (SELECT REPLACE(@ModName,' LTE',''))

  SET @ModName = (SELECT REPLACE(@ModName,' TABLET',''))

  SET @ModName = (SELECT REPLACE(@ModName,'CPO ',''))

  --manufactors

  SET @ModName = (SELECT REPLACE(@ModName,'IPHONE ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'HTC ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'LG ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'IPH ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'SAM ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'SAMSUNG ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'PALM ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'SANYO ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'NETGEAR ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'KYOCERA ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'KYO ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'FRANKLIN ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'ALCATEL ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'MOTO ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'ZTE ',''))

  ----colors last

  SET @ModName = (SELECT REPLACE(@ModName,'WHITE ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' WHITE',''))

  SET @ModName = (SELECT REPLACE(@ModName,'WHT ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' WHT',''))

  SET @ModName = (SELECT REPLACE(@ModName,'WH ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' WH',''))

  SET @ModName = (SELECT REPLACE(@ModName,'PINK ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'GOLD ',''))

  SET @ModName = (SELECT REPLACE(@ModName,'GOLD ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' GOLD',''))

  SET @ModName = (SELECT REPLACE(@ModName,'GLD ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' GLD',''))

  SET @ModName = (SELECT REPLACE(@ModName,' GD',''))

  SET @ModName = (SELECT REPLACE(@ModName,'BLK ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' BLK',''))

  SET @ModName = (SELECT REPLACE(@ModName,'BLACK ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' BLACK',''))

  SET @ModName = (SELECT REPLACE(@ModName,' BK',' '))

  SET @ModName = (SELECT REPLACE(@ModName,' BK ',' '))

  SET @ModName = (SELECT REPLACE(@ModName,'GREEN ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' GREEN',''))

  SET @ModName = (SELECT REPLACE(@ModName,'GRAY ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' GRAY',''))

  SET @ModName = (SELECT REPLACE(@ModName,'GRY ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' GRY',''))

  SET @ModName = (SELECT REPLACE(@ModName,'SILVER ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SILVER',''))

  SET @ModName = (SELECT REPLACE(@ModName,'SILV ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SILV',''))

  SET @ModName = (SELECT REPLACE(@ModName,'SLV ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SLV',''))

  SET @ModName = (SELECT REPLACE(@ModName,' SV',''))

  SET @ModName = (SELECT REPLACE(@ModName,'ROSE ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' ROSE',''))

  SET @ModName = (SELECT REPLACE(@ModName,'BLUE ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' BLUE',''))

  SET @ModName = (SELECT REPLACE(@ModName,'BLU ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' BLU',''))

  SET @ModName = (SELECT REPLACE(@ModName,'RED ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' RED',''))

  SET @ModName = (SELECT REPLACE(@ModName,'NAVY ',''))

  SET @ModName = (SELECT REPLACE(@ModName,' NAVY',''))

  SET @ModName = (SELECT REPLACE(@ModName,' ',' '))

  SET @ModName = (SELECT REPLACE(@ModName,' ',' '))

  SET @ModName = (SELECT LTRIM(RTRIM(@ModName)))

  RETURN @ModName

  END

  ---------------------------------------

  So then I use a case statement (see snipnet below)

  WHEN xxx.dbo.udf_Model_Name_Compare(l.MODEL_NAME) = xxx.dbo.udf_Model_Name_Compare(l.LOAN_MODEL_NAME) THEN 'Yes'

  ELSE 'Needs manual review of model match' END

  The question is ……….. is there a better way? This is used for every record. and sometimes that is in the 1,000+ records at a time.

  The function works very well, I am just wondering if there is a better way?

  Here's a better way, which is explained in this article: http://www.sqlservercentral.com/articles/T-SQL/91724/

  It's not the best option, but it's the best without changing the schema.

  CREATE FUNCTION [dbo].[itvf_Model_Name_Compare]

  (

  @ModName VARCHAR(100)

  )

  RETURNS TABLE WITH SCHEMABINDING

  AS

  RETURN

  SELECT LTRIM(RTRIM(

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

  @ModName

  -- it is highly recommended words be stripped out in this order

  ,' SNGL',''),' KIT',''),' KT',''),' KI',''),' SGL',''),' XSGL',''),' SNG',''),' XCVR',''),' HOTSPOT',''),' HTSPT','')

  ,' TRANSCEIVER',''),' HANDSET',''),' SG',''),' DEVICE',''),' DVC',''),' LTE',''),' TABLET',''),'CPO ',''),'IPHONE ',''),'HTC ','')

  ,'LG ',''),'IPH ',''),'SAM ',''),'SAMSUNG ',''),'PALM ',''),'SANYO ',''),'NETGEAR ',''),'KYOCERA ',''),'KYO ',''),'FRANKLIN ','')

  ,'ALCATEL ',''),'MOTO ',''),'ZTE ',''),'WHITE ',''),' WHITE',''),'WHT ',''),' WHT',''),'WH ',''),' WH',''),'PINK ','')

  ,'GOLD ',''),'GOLD ',''),' GOLD',''),'GLD ',''),' GLD',''),' GD',''),'BLK ',''),' BLK',''),'BLACK ',''),' BLACK','')

  ,' BK',' '),' BK ',' '),'GREEN ',''),' GREEN',''),'GRAY ',''),' GRAY',''),'GRY ',''),' GRY',''),'SILVER ',''),' SILVER','')

  ,'SILV ',''),' SILV',''),'SLV ',''),' SLV',''),' SV',''),'ROSE ',''),' ROSE',''),'BLUE ',''),' BLUE',''),'BLU ','')

  ,' BLU',''),'RED ',''),' RED',''),'NAVY ',''),' NAVY',''),' ',' '),' ',' '))) AS StrippedModelName;

  GO

  CREATE TABLE Table1 (MODEL_NAME varchar(100), LOAN_MODEL_NAME varchar(100))

  INSERT INTO Table1

  SELECT 'IPHONE 6S ROSE 64GB SGL ' , 'IPHONE 6S ROSE 64GB KIT ' UNION ALL

  SELECT 'IPHONE 6S ROSE 64GB SGL ' , 'IPHONE 6S ROSE 16GB KIT ' UNION ALL

  SELECT 'SAMSUNG L900 SILVER ' , 'SAMSUNG N930P BLK KIT ' UNION ALL

  SELECT 'SAM G935 32GB BK XCVR SGL' , 'SAMSUNG G935P 32GB BK KIT ' UNION ALL

  SELECT 'IPH 6S PLUS GRAY 64GB SGL' , 'IPHONE 6S PLUS GRAY 64GB KIT'

  SELECT *, CASE WHEN mn.StrippedModelName = lmn.StrippedModelName THEN 'Yes'

  ELSE 'Needs manual review of model match' END

  FROM Table1 t1

  CROSS APPLY dbo.[itvf_Model_Name_Compare](MODEL_NAME) mn

  CROSS APPLY dbo.[itvf_Model_Name_Compare](LOAN_MODEL_NAME) lmn;

  GO

  DROP TABLE Table1;

  DROP FUNCTION [itvf_Model_Name_Compare];

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  September 21, 2016 at 9:18 am

  #1902212

  just thinking out loud.....but do you have a prescribed list of the models you need to search for.....eg "6S 64GB - L900 - N930P - G935 32GB"?

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• ghughes

  SSCrazy

  Points: 2201

  More actions

  September 21, 2016 at 9:28 am

  #1902214

  I do not. I would have to build the list from the data on a regular basis since I would not know when they are going to slip in a new model. I don't really want to be in maintenance mode, although with COLORs I am stuck, since they change them ever year....

  This UDF solution was my path to the least amount of pain.......

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  September 21, 2016 at 10:16 am

  #1902228

  Nicely done Luis. I completely missed that in nesting the REPLACEs you can make them operate in the order you want and thus could wrap this up in the desired iTVF.

  I blame my oversight on just arriving in Bucharest, Romania from the central US about 24 hours ago. :hehe:

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  September 21, 2016 at 10:29 am

  #1902229

  Here's a potential solution using a recursive CTE. It's not likely to as fast as a nested replace solution in terms of performance but it would allow for easier maintenance allowing you to use a separate "bad words" table that is easier to keep up with...

  IF OBJECT_ID('tempdb..#PhoneModels', 'U') IS NOT NULL

  DROP TABLE #PhoneModels;

  SELECT

  x.Model

  INTO #PhoneModels

  FROM ( VALUES

  ('IPHONE 6S ROSE 64GB SGL'), ('IPHONE 6S ROSE 64GB KIT'),

  ('IPHONE 6S ROSE 64GB SGL'), ('IPHONE 6S ROSE 16GB KIT'),

  ('SAMSUNG L900 SILVER'), ('SAMSUNG N930P BLK KIT'),

  ('SAM G935 32GB BK XCVR SGL'), ('SAMSUNG G935P 32GB BK KIT'),

  ('IPH 6S PLUS GRAY 64GB SGL'), ('IPHONE 6S PLUS GRAY 64GB KIT')

  ) x (Model);

  IF OBJECT_ID('tempdb..#BadWords', 'U') IS NOT NULL

  DROP TABLE #BadWords;

  SELECT

  x.Word

  INTO #BadWords

  FROM ( VALUES

  (' ROSE'), (' KIT'),(' SGL'),(' SILVER'), (' BLK'), (' GRAY'),

  (' PLUS'), (' BK')

  ) x (Word);

  -------------------------------------

  -- solution

  -------------------------------------

  WITH

  cte_RecursiveReplace AS (

  SELECT

  pm.Model,

  Iteration = 1,

  NewModel = CAST(pm.Model AS VARCHAR(1000))

  FROM

  #PhoneModels pm

  UNION ALL

  SELECT

  rr.Model,

  Iteration = rr.Iteration + 1,

  Model = CAST(REPLACE(rr.NewModel, bw.Word, '') AS VARCHAR(1000))

  FROM

  cte_RecursiveReplace rr

  JOIN #BadWords bw

  ON rr.NewModel LIKE '%' + bw.Word + '%'

  )

  SELECT

  pm.Model,

  rrx.NewModel

  FROM

  #PhoneModels pm

  CROSS APPLY (

  SELECT TOP 1

  rr.NewModel

  FROM

  cte_RecursiveReplace rr

  WHERE

  pm.Model = rr.Model

  ORDER BY

  rr.Iteration DESC

  ) rrx

  results...

  Model NewModel

  ---------------------------- -------------------------------------

  IPHONE 6S ROSE 64GB SGL IPHONE 6S 64GB

  IPHONE 6S ROSE 64GB KIT IPHONE 6S 64GB

  IPHONE 6S ROSE 64GB SGL IPHONE 6S 64GB

  IPHONE 6S ROSE 16GB KIT IPHONE 6S 16GB

  SAMSUNG L900 SILVER SAMSUNG L900

  SAMSUNG N930P BLK KIT SAMSUNG N930P

  SAM G935 32GB BK XCVR SGL SAM G935 32GB XCVR

  SAMSUNG G935P 32GB BK KIT SAMSUNG G935P 32GB

  IPH 6S PLUS GRAY 64GB SGL IPH 6S 64GB

  IPHONE 6S PLUS GRAY 64GB KIT IPHONE 6S 64GB

• ghughes

  SSCrazy

  Points: 2201

  More actions

  September 21, 2016 at 11:03 am

  #1902240

  this is what I can do. I can write some sql, that will produce some tsql alter statement to a function that is driven by the bad words table.

  if I added 'score' 1,2,3 I can dynamically build the replace statement driven from the rows score. some words would be a 1, manufacture a 2, and colors a 3.

  I like it. Any thoughts?

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  September 21, 2016 at 11:37 am

  #1902252

  ghughes (9/21/2016)

  ---

  this is what I can do. I can write some sql, that will produce some tsql alter statement to a function that is driven by the bad words table.

  if I added 'score' 1,2,3 I can dynamically build the replace statement driven from the rows score. some words would be a 1, manufacture a 2, and colors a 3.

  I like it. Any thoughts?

  I have had senarios in the past that required dynamically created SQL that was table driven. And using another field (or perhaps 2) you can control the order as appropriate as well. I suppose with a trigger you could even automatically create the function on changes. Sounds like a win! :w00t:

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Sergiy

  SSC Guru

  Points: 110206

  More actions

  September 21, 2016 at 5:30 pm

  #1902318

  ghughes (9/21/2016)

  ---

  this is what I can do. I can write some sql, that will produce some tsql alter statement to a function that is driven by the bad words table.

  if I added 'score' 1,2,3 I can dynamically build the replace statement driven from the rows score. some words would be a 1, manufacture a 2, and colors a 3.

  I like it. Any thoughts?

  Do not try to put data into the code.

  Put it into tables - where it should be.

  Then you're gonna need to rebuild dynamic SQL code time after time.

  Here is the data management part:

  DECLARE @ReplacementCodes TABLE (

  priority INT NOT NULL,

  Code VARCHAR(50)

  )

  INSERT INTO @ReplacementCodes ( priority, Code )

  VALUES

  (1, ' ROSE'), (2, ' KIT'),(3, ' SGL'),(4, ' SILVER'), (5, ' BLK'), (6, ' GRAY'),(7, ' PLUS'), (8, ' BK')

  As you understand, this could (and should) be managed by users through a simple UI.

  And here is your code:

  --Declaration part:

  DECLARE @Model VARCHAR(200)

  SET @Model = 'IPHONE 6S ROSE 64GB SGL'

  --Code for the function here:

  SELECT @Model = REPLACE(@Model, code, '')

  FROM @ReplacementCodes rc

  ORDER BY rc.priority

  -- Output

  SELECT @Model

  No literal constants means no need to change it when the models list is changed.

  _____________
  Code for TallyGenerator

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  September 22, 2016 at 12:11 am

  #1902342

  Very slick Sergiy!! An elegant yet exceedingly simple solution to this need.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• GaryV

  Hall of Fame

  Points: 3307

  More actions

  September 22, 2016 at 5:43 am

  #1902407

  I like Sergiy's solution too. But what happens when you need to insert a new replacement code between priority 3 and 4?

• Sergiy

  SSC Guru

  Points: 110206

  More actions

  September 22, 2016 at 6:00 am

  #1902416

  gvoshol 73146 (9/22/2016)

  ---

  I like Sergiy's solution too. But what happens when you need to insert a new replacement code between priority 3 and 4?

  Increase priority value in all rows with 4 and above by 1 and then add a new record with priority 4.

  Keep in mind - it's nor required to have unique priority values across the table.

  My script will return the same result even if all the codes will have the same priority.

  _____________
  Code for TallyGenerator

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  September 22, 2016 at 6:32 am

  #1902435

  Sergiy (9/22/2016)

  ---

  gvoshol 73146 (9/22/2016)

  ---

  I like Sergiy's solution too. But what happens when you need to insert a new replacement code between priority 3 and 4?

  Increase priority value in all rows with 4 and above by 1 and then add a new record with priority 4.

  Keep in mind - it's nor required to have unique priority values across the table.

  My script will return the same result even if all the codes will have the same priority.

  An easier option would be to use a DECIMAL (or NNUMERIC) data type for the Priority... So, if you need a number between 3 and 4, just insert 3.5...

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  September 22, 2016 at 6:48 am

  #1902448

  One issue that I would take with Sergiy's suggestion is that it can't be used in an iTVF. The resulting function will either be a scalar function or mTVF.