I need to clean up a set of records and the descriptions are filled with examples like 'part 3" screw' or 'decal; "don't use" '. I want a replace statement that says If the " is before a letter or after a letter then replace with ' ', if a " follows a number then replace with the letters 'in'. Any ideas?
This code will handle up to 3 occurrences of [number]" (such as 3" or 6") in a single pass. If there are more than 3, you'd need another pass of those rows thru this code to adjust any remaining "s.
CREATE TABLE #data (
description varchar(1000) NULL
)
INSERT INTO #data VALUES
('part 3" screw'),
('decal; "don''t use"')
SELECT
d.description,
final_description
FROM #data d
CROSS APPLY (
SELECT PATINDEX('%[0-9]"%', description) AS num_quote_byte1
) AS ca1
CROSS APPLY (
SELECT CASE WHEN num_quote_byte1 = 0 THEN description
ELSE STUFF(description, num_quote_byte1 + 1, 1, 'in') END AS description2
) AS ca2
CROSS APPLY (
SELECT PATINDEX('%[0-9]"%', description2) AS num_quote_byte2
) AS ca3
CROSS APPLY (
SELECT CASE WHEN num_quote_byte2 = 0 THEN description2
ELSE STUFF(description2, num_quote_byte2 + 1, 1, 'in') END AS description3
) AS ca4
CROSS APPLY (
SELECT PATINDEX('%[0-9]"%', description3) AS num_quote_byte3
) AS ca5
CROSS APPLY (
SELECT CASE WHEN num_quote_byte3 = 0 THEN description3
ELSE STUFF(description3, num_quote_byte3 + 1, 1, 'in') END AS description4
) AS ca6
CROSS APPLY (
SELECT CASE WHEN PATINDEX('%[0-9]"%', description4) > 0 THEN description4
ELSE REPLACE(description4, '"', ' ') END AS final_description
) AS ca7
WHERE d.description LIKE '%"%'Tried this on my record set and as an example it made ' QUICK COUPLER 3/8" W/ SAFETY ' this ' QUICK COUPLER 3/3in W/ SAFETY' Among other mistakes.
To turn my code into an UPDATE rather than just a SELECT:
UPDATE d
SET description = final_description
--SELECT d.description, final_description
FROM #data d
CROSS APPLY ( ...rest as before...i wrote this function .. it takes care of all occurences
... its also taking care of upper case and lower case letters ..
for upper and lower case to work ... the collation must be Latin1_General_CS_AS
please click arrow to the left for function
create function dbo.replace_string(@string varchar(100))
returns varchar(100)
as
BEGin
select @string=REPLaCE(REPLaCE(REPLaCE(REPLaCE(REPLaCE(REPLaCE(REPLaCE(REPLaCE(REPLaCE(REPLaCE(@string,'1"','1in'),'2"','2in'),'3"','3in'),'4"','4in'),'5"','5in'),'6"','6in'),'7"','7in'),'8"','8in'),'9"','9in'),'0"','0in')
select @string=REPLaCE(REPLaCE(@string,'"a','a'),'a"','a')
select @string=REPLaCE(REPLaCE(@string,'"b','b'),'b"','b')
select @string=REPLaCE(REPLaCE(@string,'"c','c'),'c"','c')
select @string=REPLaCE(REPLaCE(@string,'"d','d'),'d"','d')
select @string=REPLaCE(REPLaCE(@string,'"e','e'),'e"','e')
select @string=REPLaCE(REPLaCE(@string,'"f','f'),'f"','f')
select @string=REPLaCE(REPLaCE(@string,'"g','g'),'g"','g')
select @string=REPLaCE(REPLaCE(@string,'"h','h'),'h"','h')
select @string=REPLaCE(REPLaCE(@string,'"i','i'),'i"','i')
select @string=REPLaCE(REPLaCE(@string,'"j','j'),'j"','j')
select @string=REPLaCE(REPLaCE(@string,'"k','k'),'k"','k')
select @string=REPLaCE(REPLaCE(@string,'"l','l'),'l"','l')
select @string=REPLaCE(REPLaCE(@string,'"m','m'),'m"','m')
select @string=REPLaCE(REPLaCE(@string,'"n','n'),'n"','n')
select @string=REPLaCE(REPLaCE(@string,'"p','p'),'p"','p')
select @string=REPLaCE(REPLaCE(@string,'"q','q'),'q"','q')
select @string=REPLaCE(REPLaCE(@string,'"r','r'),'r"','r')
select @string=REPLaCE(REPLaCE(@string,'"s','s'),'s"','s')
select @string=REPLaCE(REPLaCE(@string,'"t','t'),'t"','t')
select @string=REPLaCE(REPLaCE(@string,'"u','u'),'u"','u')
select @string=REPLaCE(REPLaCE(@string,'"v','v'),'v"','v')
select @string=REPLaCE(REPLaCE(@string,'"w','w'),'w"','w')
select @string=REPLaCE(REPLaCE(@string,'"x','x'),'x"','x')
select @string=REPLaCE(REPLaCE(@string,'"y','y'),'y"','y')
select @string=REPLaCE(REPLaCE(@string,'"z','z'),'z"','z')
select @string=REPLaCE(REPLaCE(@string,'"A','A'),'A"','A')
select @string=REPLaCE(REPLaCE(@string,'"B','B'),'B"','B')
select @string=REPLaCE(REPLaCE(@string,'"C','C'),'C"','C')
select @string=REPLaCE(REPLaCE(@string,'"D','D'),'D"','D')
select @string=REPLaCE(REPLaCE(@string,'"E','E'),'E"','E')
select @string=REPLaCE(REPLaCE(@string,'"F','F'),'F"','F')
select @string=REPLaCE(REPLaCE(@string,'"G','G'),'G"','G')
select @string=REPLaCE(REPLaCE(@string,'"H','H'),'H"','H')
select @string=REPLaCE(REPLaCE(@string,'"I','I'),'I"','I')
select @string=REPLaCE(REPLaCE(@string,'"J','J'),'J"','J')
select @string=REPLaCE(REPLaCE(@string,'"K','K'),'K"','K')
select @string=REPLaCE(REPLaCE(@string,'"L','L'),'L"','L')
select @string=REPLaCE(REPLaCE(@string,'"M','M'),'M"','M')
select @string=REPLaCE(REPLaCE(@string,'"N','N'),'N"','N')
select @string=REPLaCE(REPLaCE(@string,'"O','O'),'O"','O')
select @string=REPLaCE(REPLaCE(@string,'"P','P'),'P"','P')
select @string=REPLaCE(REPLaCE(@string,'"Q','Q'),'Q"','Q')
select @string=REPLaCE(REPLaCE(@string,'"R','R'),'R"','R')
select @string=REPLaCE(REPLaCE(@string,'"S','S'),'S"','S')
select @string=REPLaCE(REPLaCE(@string,'"T','T'),'T"','T')
select @string=REPLaCE(REPLaCE(@string,'"U','U'),'U"','U')
select @string=REPLaCE(REPLaCE(@string,'"V','V'),'V"','V')
select @string=REPLaCE(REPLaCE(@string,'"W','W'),'W"','W')
select @string=REPLaCE(REPLaCE(@string,'"X','X'),'X"','X')
select @string=REPLaCE(REPLaCE(@string,'"Y','Y'),'Y"','Y')
select @string=REPLaCE(REPLaCE(@string,'"Z','Z'),'Z"','Z')
RETURN @string
END
please click arrow for sample data script
create table temp
(
record varchar(100)
)
go
insert into temp values
('part 3" screw'),
('decal; "don''t use"'),
(' may be "A so 6" then k" fine')
select
record
, dbo.replace_string(record)
from
temp
is this related to an asset management, inventory system? Why is it that you are stuffing so much business rules data in one column?
So let's say you have 20 different 3inch screws
(https://www.homedepot.com/b/Hardware-Fasteners-Screws-Wood-Screws/3-in/N-5yc1vZc2blZ1z0sg13)
And eventually you do a report on 3 inch screws, wouldn't it better to define that in a separate product.size column or a product.properties table? otherwise you will be still be parsing a description column to get any meaningful, accurate and scalable process. This approach is bound to come bite you in the bootay in the long run
Thank you so much for this function. It's works in my SQL database. But I didn't see the full picture. The file I am working with are parts price files. And I typically alter them outside of the main SQL db thru Navicat. Which is MYSQL. So now I'm trying to figure out how to write a your function in my MYSQL. Replace is a valid function however it errors as
"...check the manual that corresponds to your MYSQL server version for the right syntax near ')
Returns Varchar(250)
Begin
select @string=REPLaCE(REPLaCE(REPLaCE(REPLaCE' at line 1 "
I forgot that MYSQL is not the same as SQL.
hi
mySQL has this function replace
you will have to start debugging !!! ( part by part line by line )
Example
select 1 a
select 2 union xyz
select 3 sa
first select 1 works or debug
then add a
select 1 a
then make it work or debug add next statement
select 1 a
select 2 union xyz ( make it work or fix it )
Yes replace is a function in MYSQL but for some reason it errors, as indicated in previous response, when I use it as part of a Scalar function. I have no idea why. What I did was put all my records in in a temp file in SQL, run your function, export it. Import into Navicat, running mysql, run my query, export it, and import into proprietary softaware for parts pricing. Kinda a long way around. Hopefully I won't have to do this but once a year.