Function to find and replace from a string where like or contains

Question

Post reply

Function to find and replace from a string where like or contains

Northern Monkey

Ten Centuries

Points: 1017
More actions
December 3, 2015 at 10:24 am

#306856

Hi All
I have a function that will replace from a column that contains just one value but now I need to to find a value from a list of colours so if the colour is found it replaces it with '' here is the other version for the single column
Amy help would be well appreciated 🙂
ALTER function [dbo].[UfnLikeColour]
(@Value varchar(max))
returns varchar(max)
as
begin
declare @returnstring varchar(max)
set @returnstring = lower(@Value)
declare @i int
set @i = ascii('a')
while @i <= ascii('z')
begin
set @returnstring = replace( @returnstring, ' ' + char(@i), ' ' + char(@i-32))
set @i = @i + 1
end
set @returnstring = CASEWHENchar(ascii(left(@returnstring, 1))-32) + right(@returnstring, len(@returnstring)-1) IN
(
'AliceBlue',
'AntiqueWhite',
'Aqua',
'Aquamarine',
'Azure',
'Beige',
'Bisque',
'Black',
'BlanchedAlmond',
'Blue',
'BlueViolet',
'Brown',
'BurlyWood',
'CadetBlue',
'Chartreuse',
'Chocolate',
'Coral',
'CornflowerBlue',
'Cornsilk',
'Crimson',
'Cyan',
'DarkBlue',
'DarkCyan',
'DarkGoldenRod',
'DarkGray',
'DarkGreen',
'DarkKhaki',
'DarkMagenta',
'DarkOliveGreen',
'DarkOrange',
'DarkOrchid',
'DarkRed',
'DarkSalmon',
'DarkSeaGreen',
'DarkSlateBlue',
'DarkSlateGray',
'DarkTurquoise',
'DarkViolet',
'DeepPink',
'DeepSkyBlue',
'DimGray',
'DodgerBlue',
'FireBrick',
'FloralWhite',
'ForestGreen',
'Fuchsia',
'Gainsboro',
'GhostWhite',
'Gold',
'GoldenRod',
'Gray',
'Green',
'GreenYellow',
'HoneyDew',
'HotPink',
'IndianRed ',
'Indigo ',
'Ivory',
'Khaki',
'Lavender',
'LavenderBlush',
'LawnGreen',
'LemonChiffon',
'LightBlue',
'LightCoral',
'LightCyan',
'LightGoldenRodYellow',
'LightGray',
'LightGreen',
'LightPink',
'LightSalmon',
'LightSeaGreen',
'LightSkyBlue',
'LightSlateGray',
'LightSteelBlue',
'LightYellow',
'Lime',
'LimeGreen',
'Linen',
'Magenta',
'Maroon',
'MediumAquaMarine',
'MediumBlue',
'MediumOrchid',
'MediumPurple',
'MediumSeaGreen',
'MediumSlateBlue',
'MediumSpringGreen',
'MediumTurquoise',
'MediumVioletRed',
'MidnightBlue',
'MintCream',
'MistyRose',
'Moccasin',
'NavajoWhite',
'Navy',
'Nude',
'OldLace',
'Olive',
'OliveDrab',
'Orange',
'OrangeRed',
'Orchid',
'PaleGoldenRod',
'PaleGreen',
'PaleTurquoise',
'PaleVioletRed',
'PapayaWhip',
'PeachPuff',
'Peru',
'Pink',
'Plum',
'PowderBlue',
'Purple',
'RebeccaPurple',
'Red',
'RosyBrown',
'RoyalBlue',
'SaddleBrown',
'Salmon',
'SandyBrown',
'SeaGreen',
'SeaShell',
'Sienna',
'Silver',
'SkyBlue',
'Skin Tone',
'SkinTone', -- --- --- make this with the space
'SlateBlue',
'SlateGray',
'Snow',
'SpringGreen',
'SteelBlue',
'Tan',
'Teal',
'Thistle',
'Tomato',
'Turquoise',
'Violet',
'Wheat',
'White',
'WhiteSmoke',
'Yellow',
'YellowGreen',
'Assorted Colours',
'Black Combination',
'Black Currant',
'Black/Nude',
'Black/Pink',
'Black/Pink Ash',
'Black/Skin',
'Black/White',
'Blossom',
'Bluebell',
'Blush',
'Botanical',
'Cappuccino',
'Caramel',
'Champagne',
'Cherry Red',
'Deep Blue',
'Emerald',
'Flamenco',
'Floral Print',
'Grape',
'Grey',
'Holly Berry',
'Ivory / Skin',
'Ivory/Floral',
'Ivory/Pink',
'Ivory/Rose',
'Leopard Print',
'Lollipop',
'Midnight Blue',
'Mocha',
'Monochrome',
'Navy Floral',
'Neon',
'Neon Pink',
'Nude Beige',
'Nude/Pink',
'Peppermint',
'Pink Paradise',
'Powder',
'Raspberry',
'Rebel',
'Rose',
'Rose Pink',
'Sea Breeze',
'Skin',
'Skin/Light',
'Smooth Skin',
'Spearmint',
'Tangerine',
'Vanille',
'White/Print',
'White/Skin Tone'
)
THEN char(ascii(left(@returnstring, 1))-32) + right(@returnstring, len(@returnstring)-1)
ELSE 'No Colour'
END
return @returnstring
end

Viewing 13 posts - 1 through 12 (of 12 total)

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

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

Can you give some examples of your inputs and ouputs?

You added a lot of work when it's not needed. Your function can be reduced to a single statement.

Do you have a case sensitive database?

Why don't you store the list of colors in a table?

Example of your function:

ALTER FUNCTION dbo.UfnLikeColour(

@Value VARCHAR(8000)) --What color name has over 8000 characters?

RETURNS VARCHAR(8000)

AS

BEGIN

RETURN CASE

WHEN @Value IN( 'AliceBlue', 'AntiqueWhite', 'Aqua', 'Aquamarine', 'Azure', 'Beige', 'Bisque', 'Black', 'BlanchedAlmond', 'Blue', 'BlueViolet', 'Brown', 'BurlyWood', 'CadetBlue', 'Chartreuse', 'Chocolate', 'Coral', 'CornflowerBlue', 'Cornsilk', 'Crimson', 'Cyan', 'DarkBlue', 'DarkCyan', 'DarkGoldenRod', 'DarkGray', 'DarkGreen', 'DarkKhaki', 'DarkMagenta', 'DarkOliveGreen', 'DarkOrange', 'DarkOrchid', 'DarkRed', 'DarkSalmon', 'DarkSeaGreen', 'DarkSlateBlue', 'DarkSlateGray', 'DarkTurquoise', 'DarkViolet', 'DeepPink', 'DeepSkyBlue', 'DimGray', 'DodgerBlue', 'FireBrick', 'FloralWhite', 'ForestGreen', 'Fuchsia', 'Gainsboro', 'GhostWhite', 'Gold', 'GoldenRod', 'Gray', 'Green', 'GreenYellow', 'HoneyDew', 'HotPink', 'IndianRed ', 'Indigo ', 'Ivory', 'Khaki', 'Lavender', 'LavenderBlush', 'LawnGreen', 'LemonChiffon', 'LightBlue', 'LightCoral', 'LightCyan', 'LightGoldenRodYellow', 'LightGray', 'LightGreen', 'LightPink', 'LightSalmon', 'LightSeaGreen', 'LightSkyBlue', 'LightSlateGray', 'LightSteelBlue', 'LightYellow', 'Lime', 'LimeGreen', 'Linen', 'Magenta', 'Maroon', 'MediumAquaMarine', 'MediumBlue', 'MediumOrchid', 'MediumPurple', 'MediumSeaGreen', 'MediumSlateBlue', 'MediumSpringGreen', 'MediumTurquoise', 'MediumVioletRed', 'MidnightBlue', 'MintCream', 'MistyRose', 'Moccasin', 'NavajoWhite', 'Navy', 'Nude', 'OldLace', 'Olive', 'OliveDrab', 'Orange', 'OrangeRed', 'Orchid', 'PaleGoldenRod', 'PaleGreen', 'PaleTurquoise', 'PaleVioletRed', 'PapayaWhip', 'PeachPuff', 'Peru', 'Pink', 'Plum', 'PowderBlue', 'Purple', 'RebeccaPurple', 'Red', 'RosyBrown', 'RoyalBlue', 'SaddleBrown', 'Salmon', 'SandyBrown', 'SeaGreen', 'SeaShell', 'Sienna', 'Silver', 'SkyBlue', 'Skin Tone', 'SkinTone', -- --- --- make this with the space

'SlateBlue', 'SlateGray', 'Snow', 'SpringGreen', 'SteelBlue', 'Tan', 'Teal', 'Thistle', 'Tomato', 'Turquoise', 'Violet', 'Wheat', 'White', 'WhiteSmoke', 'Yellow', 'YellowGreen', 'Assorted Colours', 'Black Combination', 'Black Currant', 'Black/Nude', 'Black/Pink', 'Black/Pink Ash', 'Black/Skin', 'Black/White', 'Blossom', 'Bluebell', 'Blush', 'Botanical', 'Cappuccino', 'Caramel', 'Champagne', 'Cherry Red', 'Deep Blue', 'Emerald', 'Flamenco', 'Floral Print', 'Grape', 'Grey', 'Holly Berry', 'Ivory / Skin', 'Ivory/Floral', 'Ivory/Pink', 'Ivory/Rose', 'Leopard Print', 'Lollipop', 'Midnight Blue', 'Mocha', 'Monochrome', 'Navy Floral', 'Neon', 'Neon Pink', 'Nude Beige', 'Nude/Pink', 'Peppermint', 'Pink Paradise', 'Powder', 'Raspberry', 'Rebel', 'Rose', 'Rose Pink', 'Sea Breeze', 'Skin', 'Skin/Light', 'Smooth Skin', 'Spearmint', 'Tangerine', 'Vanille', 'White/Print', 'White/Skin Tone' )

THEN UPPER(LEFT(@value, 1)) + LOWER(RIGHT(@value, LEN(@value) - 1))

ELSE 'No Colour'

END;

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

First, I don't know if you noticed or understood the motivation by Luis' comment in his code about why he changed it to varchar(8000) from varchar(max). You only want to use MAX data types when you absolutely have to. There is a almost always a performance penalty for MAX data types.

Next, I'm going to assume that when @Value is "Aliceblue" or "AliceBLUE" you want the function to return Aliceblue; for "Skin tone" or "skin tone" you want "Skin Tone"; for "black/white" you want "Black/White". In other words, whatever is specified in your color list is the exact format you want. For fun we'll add: "Mocha LollyPop Special/Special".

With a little KISS we can do this without any string manipulation like so:

ALTER FUNCTION dbo.[UfnLikeColour] (@Value varchar(8000))

RETURNS varchar(8000) AS

BEGIN

RETURN

(

SELECT ISNULL(MAX(c),'No Colour')

FROM

(VALUES('AliceBlue'),('AntiqueWhite'),('Aqua'),('Aquamarine'),('Azure'),('Beige'),('Bisque'),('Black'),('BlanchedAlmond'),('Blue'),('BlueViolet'),('Brown'),('BurlyWood'),('CadetBlue'),('Chartreuse'),('Chocolate'),('Coral'),('CornflowerBlue'),('Cornsilk'),('Crimson'),('Cyan'),('DarkBlue'),('DarkCyan'),('DarkGoldenRod'),('DarkGray'),('DarkGreen'),('DarkKhaki'),('DarkMagenta'),('DarkOliveGreen'),('DarkOrange'),('DarkOrchid'),

('DarkRed'),('DarkSalmon'),('DarkSeaGreen'),('DarkSlateBlue'),('DarkSlateGray'),('DarkTurquoise'),('DarkViolet'),('DeepPink'),('DeepSkyBlue'),('DimGray'),('DodgerBlue'),('FireBrick'),('FloralWhite'),('ForestGreen'),('Fuchsia'),('Gainsboro'),('GhostWhite'),('Gold'),('GoldenRod'),('Gray'),('Green'),('GreenYellow'),('HoneyDew'),('HotPink'),('IndianRed '),('Indigo '),('Ivory'),('Khaki'),('Lavender'),('LavenderBlush'),

('LawnGreen'),('LemonChiffon'),('LightBlue'),('LightCoral'),('LightCyan'),('LightGoldenRodYellow'),('LightGray'),('LightGreen'),('LightPink'),('LightSalmon'),('LightSeaGreen'),('LightSkyBlue'),('LightSlateGray'),('LightSteelBlue'),('LightYellow'),('Lime'),('LimeGreen'),('Linen'),('Magenta'),('Maroon'),('MediumAquaMarine'),('MediumBlue'),('MediumOrchid'),('MediumPurple'),('MediumSeaGreen'),('MediumSlateBlue'),

('MediumSpringGreen'),('MediumTurquoise'),('MediumVioletRed'),('MidnightBlue'),('MintCream'),('MistyRose'),('Moccasin'),('NavajoWhite'),('Navy'),('Nude'),('OldLace'),('Olive'),('OliveDrab'),('Orange'),('OrangeRed'),('Orchid'),('PaleGoldenRod'),('PaleGreen'),('PaleTurquoise'),('PaleVioletRed'),('PapayaWhip'),('PeachPuff'),('Peru'),('Pink'),('Plum'),('PowderBlue'),('Purple'),('RebeccaPurple'),('Red'),('RosyBrown'),

('RoyalBlue'),('SaddleBrown'),('Salmon'),('SandyBrown'),('SeaGreen'),('SeaShell'),('Sienna'),('Silver'),('SkyBlue'),('Skin Tone'),('SkinTone'),('SlateBlue'),('SlateGray'),('Snow'),('SpringGreen'),('SteelBlue'),('Tan'),('Teal'),('Thistle'),('Tomato'),('Turquoise'),('Violet'),('Wheat'),('White'),('WhiteSmoke'),('Yellow'),('YellowGreen'),('Assorted Colours'),('Black Combination'),('Black Currant'),('Black/Nude'),

('Black/Pink'),('Black/Pink Ash'),('Black/Skin'),('Black/White'),('Blossom'),('Bluebell'),('Blush'),('Botanical'),('Cappuccino'),('Caramel'),('Champagne'),('Cherry Red'),('Deep Blue'),('Emerald'),('Flamenco'),('Floral Print'),('Grape'),('Grey'),('Holly Berry'),('Ivory / Skin'),('Ivory/Floral'),('Ivory/Pink'),('Ivory/Rose'),('Leopard Print'),('Lollipop'),('Midnight Blue'),('Mocha'),('Monochrome'),('Navy Floral'),

('Neon'),('Neon Pink'),('Nude Beige'),('Nude/Pink'),('Peppermint'),('Pink Paradise'),('Powder'),('Raspberry'),('Rebel'),('Rose'),('Rose Pink'),('Sea Breeze'),('Skin'),('Skin/Light'),('Smooth Skin'),('Spearmint'),('Tangerine'),('Vanille'),('White/Print'),('White/Skin Tone'),('Mocha LollyPop Special/Special')

) x(c)

WHERE LOWER(c) = LOWER(@value)

)

END

GO

And if performance is important than you might want to consider turning this function into an Inline Table Valued Function (AKA "inline scalar UDF") like so:

CREATE FUNCTION dbo.iTVFLikeColour (@Value varchar(8000))

RETURNS TABLE AS RETURN

(

SELECT color = ISNULL(MAX(c),'No Colour')

FROM

(VALUES('AliceBlue'),('AntiqueWhite'),('Aqua'),('Aquamarine'),('Azure'),('Beige'),('Bisque'),('Black'),('BlanchedAlmond'),('Blue'),('BlueViolet'),('Brown'),('BurlyWood'),('CadetBlue'),('Chartreuse'),('Chocolate'),('Coral'),('CornflowerBlue'),('Cornsilk'),('Crimson'),('Cyan'),('DarkBlue'),('DarkCyan'),('DarkGoldenRod'),('DarkGray'),('DarkGreen'),('DarkKhaki'),('DarkMagenta'),('DarkOliveGreen'),('DarkOrange'),('DarkOrchid'),

('DarkRed'),('DarkSalmon'),('DarkSeaGreen'),('DarkSlateBlue'),('DarkSlateGray'),('DarkTurquoise'),('DarkViolet'),('DeepPink'),('DeepSkyBlue'),('DimGray'),('DodgerBlue'),('FireBrick'),('FloralWhite'),('ForestGreen'),('Fuchsia'),('Gainsboro'),('GhostWhite'),('Gold'),('GoldenRod'),('Gray'),('Green'),('GreenYellow'),('HoneyDew'),('HotPink'),('IndianRed '),('Indigo '),('Ivory'),('Khaki'),('Lavender'),('LavenderBlush'),

('LawnGreen'),('LemonChiffon'),('LightBlue'),('LightCoral'),('LightCyan'),('LightGoldenRodYellow'),('LightGray'),('LightGreen'),('LightPink'),('LightSalmon'),('LightSeaGreen'),('LightSkyBlue'),('LightSlateGray'),('LightSteelBlue'),('LightYellow'),('Lime'),('LimeGreen'),('Linen'),('Magenta'),('Maroon'),('MediumAquaMarine'),('MediumBlue'),('MediumOrchid'),('MediumPurple'),('MediumSeaGreen'),('MediumSlateBlue'),

('MediumSpringGreen'),('MediumTurquoise'),('MediumVioletRed'),('MidnightBlue'),('MintCream'),('MistyRose'),('Moccasin'),('NavajoWhite'),('Navy'),('Nude'),('OldLace'),('Olive'),('OliveDrab'),('Orange'),('OrangeRed'),('Orchid'),('PaleGoldenRod'),('PaleGreen'),('PaleTurquoise'),('PaleVioletRed'),('PapayaWhip'),('PeachPuff'),('Peru'),('Pink'),('Plum'),('PowderBlue'),('Purple'),('RebeccaPurple'),('Red'),('RosyBrown'),

('RoyalBlue'),('SaddleBrown'),('Salmon'),('SandyBrown'),('SeaGreen'),('SeaShell'),('Sienna'),('Silver'),('SkyBlue'),('Skin Tone'),('SkinTone'),('SlateBlue'),('SlateGray'),('Snow'),('SpringGreen'),('SteelBlue'),('Tan'),('Teal'),('Thistle'),('Tomato'),('Turquoise'),('Violet'),('Wheat'),('White'),('WhiteSmoke'),('Yellow'),('YellowGreen'),('Assorted Colours'),('Black Combination'),('Black Currant'),('Black/Nude'),

('Black/Pink'),('Black/Pink Ash'),('Black/Skin'),('Black/White'),('Blossom'),('Bluebell'),('Blush'),('Botanical'),('Cappuccino'),('Caramel'),('Champagne'),('Cherry Red'),('Deep Blue'),('Emerald'),('Flamenco'),('Floral Print'),('Grape'),('Grey'),('Holly Berry'),('Ivory / Skin'),('Ivory/Floral'),('Ivory/Pink'),('Ivory/Rose'),('Leopard Print'),('Lollipop'),('Midnight Blue'),('Mocha'),('Monochrome'),('Navy Floral'),

('Neon'),('Neon Pink'),('Nude Beige'),('Nude/Pink'),('Peppermint'),('Pink Paradise'),('Powder'),('Raspberry'),('Rebel'),('Rose'),('Rose Pink'),('Sea Breeze'),('Skin'),('Skin/Light'),('Smooth Skin'),('Spearmint'),('Tangerine'),('Vanille'),('White/Print'),('White/Skin Tone')

) x(c)

WHERE LOWER(c) = LOWER(@value)

);

GO

This is a technique I learned here[/url]. Using this version of the function will be a little trickier to use because you need to also use APPLY but the performance will blow the doors of the scalar udf version.

Lastly, you might want to consider keeping those colors in a table an pointing the function to the table. That way you can index it for better performance (likely, I don't have time to test) and for manageability and re-usability.

Edit: Added comment about storing values in a table.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sean Lange SSC Guru Points: 286562 More actions · Answer 3

Alan.B (12/3/2015)

I am curious about a couple things here.

Why MAX(c)?

(MAX(c),'No Colour')

Also, why wrap the column and the variable with LOWER? Unless this is a case sensitive collation this provide any benefit.

WHERE LOWER(c) = LOWER(@value)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

Sean Lange (12/3/2015)
Alan.B (12/3/2015)
I am curious about a couple things here.
Why MAX(c)?
(MAX(c),'No Colour')

To always return a row, either with a value or a null. I might have done it different, but I have to admit this is a nice approach.

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

Sean Lange (12/3/2015)
Alan.B (12/3/2015)
I am curious about a couple things here.
Why MAX(c)?
(MAX(c),'No Colour')

I usually do that to get a single value but don't have a clue why I did that. My only explanation is that, in front of me is an empty glass of tea where there's normally an empty cup of coffee.

Also, why wrap the column and the variable with LOWER? Unless this is a case sensitive collation this provide any benefit.
WHERE LOWER(c) = LOWER(@value)

I'm currently working in a case sensitive environment so that's where my brain is today:hehe:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sean Lange SSC Guru Points: 286562 More actions · Answer 6

Luis Cazares (12/3/2015)
Sean Lange (12/3/2015)
Alan.B (12/3/2015)
I am curious about a couple things here.
Why MAX(c)?
(MAX(c),'No Colour')
To always return a row, either with a value or a null. I might have done it different, but I have to admit this is a nice approach.

Ahh I see now what is happening there. Kind of a new approach to that. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61136 More actions · Answer 7

Luis Cazares (12/3/2015)
Sean Lange (12/3/2015)
Alan.B (12/3/2015)
I am curious about a couple things here.
Why MAX(c)?
(MAX(c),'No Colour')
To always return a row, either with a value or a null. I might have done it different, but I have to admit this is a nice approach.

That's why, that's why! Thanks Luis - I forgot why I did that when Sean asked.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Northern Monkey Ten Centuries Points: 1017 More actions · Answer 8

Hi Luis

I need to remove any reference to colour from this string column

ie this "Calvin Klein Men's 3 Pack Trunks - Black/White/Grey Multi" would become this "Calvin Klein Men's 3 Pack Trunks - Multi"

Re field length of this data is from external source of which there are many and ever growing I may also want to use it on the product description column (some are like war and peace)

Thanks all for the help so far

S

product_name

Calvin Klein Men's 3 Pack Trunks - Black/White/Grey Multi

Leg Avenue Stockings 8291

Womens Natural Tan Premium Lace Bodyfree Tights, natural tan

Calvin Klein Men's 3 Pack Trunks - Black/White/Grey Multi

Leg Avenue Tights 7728

Blue Mardi Gras Balcony Bra, blue

Calvin Klein Men's 3 Pack Trunks - Black

Leg Avenue Tights 7525

Womens Natural Tan Premium Lace Bodyfree Tights, natural tan

Calvin Klein Men's 3 Pack Trunks - Black/White/Grey Multi

Leg Avenue Stockings 1019

Womens Multi Premium 3 Pack of Floral Mesh Ankle Socks, white/multi

Calvin Klein Men's 3 Pack Trunks - Black

Leg Avenue Stockings 6672 Blue

Womens Black & White Premium 3 Pack of Floral Mesh Ankle Socks, black/white

Calvin Klein Men's 3 Pack Trunks - Black

Leg Avenue Stockings 9064 Turquoise

Womens White Satin Reversiable Chemise, white

Calvin Klein Men's 3 Pack Trunks - Black

Leg Avenue Stockings 9072

Northern Monkey Ten Centuries Points: 1017 More actions · Answer 9

PS I would add the list of colours in to a table at some point just short on time at the moment 🙁

Northern Monkey Ten Centuries Points: 1017 More actions · Answer 10

Northern Monkey

Ten Centuries

Points: 1017

December 5, 2015 at 2:23 am

#1844197

Any more help please!

Jason A. Long SSC-Insane Points: 23731 More actions · Answer 11

Something like this perhaps???

CREATE FUNCTION dbo.tfn_LikeColour

(

@Value VARCHAR(8000)

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH ColourList AS ( -- replace this section with your Colour table when the time comes.

SELECT

x.Colour

FROM

( VALUES ( 'AliceBlue'), ( 'AntiqueWhite'), ( 'Aqua'), ( 'Aquamarine'), ( 'Azure'), ( 'Beige'), ( 'Bisque'), ( 'Black'), ( 'BlanchedAlmond'), ( 'Blue'),

( 'BlueViolet'), ( 'Brown'), ( 'BurlyWood'), ( 'CadetBlue'), ( 'Chartreuse'), ( 'Chocolate'), ( 'Coral'), ( 'CornflowerBlue'), ( 'Cornsilk'), ( 'Crimson'),

( 'Cyan'), ( 'DarkBlue'), ( 'DarkCyan'), ( 'DarkGoldenRod'), ( 'DarkGray'), ( 'DarkGreen'), ( 'DarkKhaki'), ( 'DarkMagenta'), ( 'DarkOliveGreen'),

( 'DarkOrange'), ( 'DarkOrchid'), ( 'DarkRed'), ( 'DarkSalmon'), ( 'DarkSeaGreen'), ( 'DarkSlateBlue'), ( 'DarkSlateGray'), ( 'DarkTurquoise'),

( 'DarkViolet'), ( 'DeepPink'), ( 'DeepSkyBlue'), ( 'DimGray'), ( 'DodgerBlue'), ( 'FireBrick'), ( 'FloralWhite'), ( 'ForestGreen'), ( 'Fuchsia'),

( 'Gainsboro'), ( 'GhostWhite'), ( 'Gold'), ( 'GoldenRod'), ( 'Gray'), ( 'Green'), ( 'GreenYellow'), ( 'HoneyDew'), ( 'HotPink'), ( 'IndianRed '),

( 'Indigo '), ( 'Ivory'), ( 'Khaki'), ( 'Lavender'), ( 'LavenderBlush'), ( 'LawnGreen'), ( 'LemonChiffon'), ( 'LightBlue'), ( 'LightCoral'), ( 'LightCyan'),

( 'LightGoldenRodYellow'), ( 'LightGray'), ( 'LightGreen'), ( 'LightPink'), ( 'LightSalmon'), ( 'LightSeaGreen'), ( 'LightSkyBlue'), ( 'LightSlateGray'),

( 'LightSteelBlue'), ( 'LightYellow'), ( 'Lime'), ( 'LimeGreen'), ( 'Linen'), ( 'Magenta'), ( 'Maroon'), ( 'MediumAquaMarine'), ( 'MediumBlue'),

( 'MediumOrchid'), ( 'MediumPurple'), ( 'MediumSeaGreen'), ( 'MediumSlateBlue'), ( 'MediumSpringGreen'), ( 'MediumTurquoise'), ( 'MediumVioletRed'),

( 'MidnightBlue'), ( 'MintCream'), ( 'MistyRose'), ( 'Moccasin'), ( 'NavajoWhite'), ( 'Navy'), ( 'Nude'), ( 'OldLace'), ( 'Olive'), ( 'OliveDrab'),

( 'Orange'), ( 'OrangeRed'), ( 'Orchid'), ( 'PaleGoldenRod'), ( 'PaleGreen'), ( 'PaleTurquoise'), ( 'PaleVioletRed'), ( 'PapayaWhip'), ( 'PeachPuff'),

( 'Peru'), ( 'Pink'), ( 'Plum'), ( 'PowderBlue'), ( 'Purple'), ( 'RebeccaPurple'), ( 'Red'), ( 'RosyBrown'), ( 'RoyalBlue'), ( 'SaddleBrown'), ( 'Salmon'),

( 'SandyBrown'), ( 'SeaGreen'), ( 'SeaShell'), ( 'Sienna'), ( 'Silver'), ( 'SkyBlue'), ( 'Skin Tone'), ( 'SkinTone'), ( 'SlateBlue'), ( 'SlateGray'),

( 'Snow'), ( 'SpringGreen'), ( 'SteelBlue'), ( 'Tan'), ( 'Teal'), ( 'Thistle'), ( 'Tomato'), ( 'Turquoise'), ( 'Violet'), ( 'Wheat'), ( 'White'),

( 'WhiteSmoke'), ( 'Yellow'), ( 'YellowGreen'), ( 'Assorted Colours'), ( 'Black Combination'), ( 'Black Currant'), ( 'Black/Nude'), ( 'Black/Pink'),

( 'Black/Pink Ash'), ( 'Black/Skin'), ( 'Black/White'), ( 'Blossom'), ( 'Bluebell'), ( 'Blush'), ( 'Botanical'), ( 'Cappuccino'), ( 'Caramel'),

( 'Champagne'), ( 'Cherry Red'), ( 'Deep Blue'), ( 'Emerald'), ( 'Flamenco'), ( 'Floral Print'), ( 'Grape'), ( 'Grey'), ( 'Holly Berry'), ( 'Ivory / Skin'),

( 'Ivory/Floral'), ( 'Ivory/Pink'), ( 'Ivory/Rose'), ( 'Leopard Print'), ( 'Lollipop'), ( 'Midnight Blue'), ( 'Mocha'), ( 'Monochrome'), ( 'Navy Floral'),

( 'Neon'), ( 'Neon Pink'), ( 'Nude Beige'), ( 'Nude/Pink'), ( 'Peppermint'), ( 'Pink Paradise'), ( 'Powder'), ( 'Raspberry'), ( 'Rebel'), ( 'Rose'),

( 'Rose Pink'), ( 'Sea Breeze'), ( 'Skin'), ( 'Skin/Light'), ( 'Smooth Skin'), ( 'Spearmint'), ( 'Tangerine'), ( 'Vanille'), ( 'White/Print'),

( 'White/Skin Tone')

) x (Colour)

), ColoursInScope AS (

SELECT

ROW_NUMBER() OVER (ORDER BY LEN(cl.Colour) DESC) AS rn,-- prevents "Yellow" from butchering up "LightGoldenRodYellow"..

cl.Colour

FROM

ColourList cl

WHERE

@Value LIKE '%' + cl.Colour + '%'

), RecursiveReplace AS (

SELECT

REPLACE(@Value, cis.Colour, '''''') AS ReturnString,

cis.rn

FROM

ColoursInScope cis

WHERE

cis.rn = 1

UNION ALL

SELECT

REPLACE(rr.ReturnString, cis.Colour, '''''') AS ReturnString,

cis.rn

FROM

ColoursInScope cis

JOIN RecursiveReplace rr

ON cis.rn = rr.rn + 1

)

SELECT TOP 1

rr.ReturnString

FROM

RecursiveReplace rr

ORDER BY

rr.rn DESC;

GO

Test the new function...

SELECT lc.ReturnString FROM dbo.tfn_LikeColour('You may like the following colours: LightGoldenRodYellow, Yellow, Magenta, DarkSlateBlue, Blue or Bisque.') lc

The output...

You may like the following colours: '', '', '', '', '' or ''.

Jason A. Long SSC-Insane Points: 23731 More actions · Answer 12

You'll note that the vast majority of the expense of the function above is tied to a sort operation (90%). This can be alleviated by adding a persisted computed column to your table and then indexing it.

Something like the following...

IF OBJECT_ID('dbo.ColourList', 'U') IS NOT NULL

DROP TABLE dbo.ColourList;

CREATE TABLE dbo.ColourList (

ID INT NOT NULL IDENTITY(1,1),

ColourName VARCHAR(100) NOT NULL,

ColourNameLen AS LEN(ColourName) PERSISTED,

CONSTRAINT pk_ColourList PRIMARY KEY CLUSTERED (ID ASC)

);

CREATE NONCLUSTERED INDEX ix_ColourList_ColourNameLen_ColourName ON dbo.ColourList (ColourNameLen, ColourName);

INSERT dbo.ColourList (ColourName)

VALUES ( 'AliceBlue'), ( 'AntiqueWhite'), ( 'Aqua'), ( 'Aquamarine'), ( 'Azure'), ( 'Beige'), ( 'Bisque'), ( 'Black'), ( 'BlanchedAlmond'), ( 'Blue'),