SQL removing characters

Question

SQL removing characters

JasonGerding

SSC Enthusiast

Points: 174
More actions
December 16, 2020 at 8:29 pm

#3821348

I am using the below case statement
, CASE WHEN Attributes LIKE ',Size = %,' Then right(Attributes, len(Attributes) - charindex('Size ', Attributes)) ELSE '' END as Size
To get the below result
,Size = XXL ,Fits to Chest Size = 48 to 50 in,,Closure Type = Snap Button ,Material = Cotton ,Color = Khaki ,Sleeve Length = 33 in,Lining Material = Polyester Thread ,,Fabric Weight = 9 oz,,,Resists = Flame ,,,,,,,,,,,,,,,,,,,,,,,,,,
I want to remove everything and only have Size = XXL
Any help is appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

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

ratbak Ten Centuries Points: 1379 More actions · Answer 1

The following seems to work. Add TRIM() if desired fix space before comma.

DROP TABLE IF EXISTS #attributes
CREATE TABLE #attributes
    (
    Attributes VARCHAR(MAX)
    );

INSERT INTO #attributes (Attributes)
VALUES (',Size = XXL ,Fits to Chest Size = 48 to 50 in,,Closure Type = Snap Button ,Material = Cotton ,Color = Khaki ,Sleeve Length = 33 in,Lining Material = Polyester Thread ,,Fabric Weight = 9 oz,,,Resists = Flame');


SELECT CHARINDEX(',',Attributes,2), 
    CASE WHEN Attributes LIKE ',Size = %' Then SUBSTRING(Attributes,2,CHARINDEX(',',Attributes,2) - 2) ELSE '' END as Size
FROM #attributes;

The simplicity of this approach seems less convoluted, but might be less efficient at scale:

SELECT attributes.value 
FROM #attributes
CROSS APPLY STRING_SPLIT(#attributes.Attributes, ',') attributes
WHERE value LIKE 'Size = %';

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

I think you are missing a "LEFT" on your RIGHT. You have the RIGHT side of the string, but have too much. So, what you probably want is:

LEFT(right(Attributes, len(Attributes) - charindex('Size ', Attributes)-1),charindex(',',right(Attributes, len(Attributes) - charindex('Size ', Attributes)-1))

NOTE - I did not test that.

What I changed:

added a "-1" onto the number of characters it grabs from the "RIGHT" command to strip off that first comma that appeared in your result and tossed that into a LEFT where we grab all the characters up to the first comma. This SHOULD take your",Size = XXL ,Fits to Chest..." string and give you "Size = XXL ". If you do an RTRIM on that resulting string, that would remove the trailing space. LTRIM would remove any leading spaces. Can't remember if SQL 2017 supports TRIM or if that was a 2019 feature, but LTRIM and RTRIM have been around for ages.

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.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

JasonGerding wrote:

I am using the below case statement
, CASE WHEN Attributes LIKE ',Size = %,' Then right(Attributes, len(Attributes) - charindex('Size ', Attributes)) ELSE '' END as Size
To get the below result
,Size = XXL ,Fits to Chest Size = 48 to 50 in,,Closure Type = Snap Button ,Material = Cotton ,Color = Khaki ,Sleeve Length = 33 in,Lining Material = Polyester Thread ,,Fabric Weight = 9 oz,,,Resists = Flame ,,,,,,,,,,,,,,,,,,,,,,,,,,
I want to remove everything and only have Size = XXL
Any help is appreciated.

Ok... lets start thinking about "bullet proofing" it.

Can the string ',Size = ' only be at the beginning of the string as you've portrayed it or any where in the string?
Will the "=" sign ALWAYS have 1 and only 1 space on either side?
Will "Size" ALWAYS be immediately preceded by a comma?

As a bit of a sidebar, I'd seriously consider turning this into an EAV table to make future look ups of all sorts a whole lot easier while having a chance of the code actually being SARGable for look ups.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101108 More actions · Answer 4

I added an RTRIM to get rid of any trailing space(s). Naturally if you don't want to do that, delete the RTRIM from the code.

SELECT 
    Attributes,
    RTRIM(SUBSTRING(Attributes, size_start + 1, CHARINDEX(',', Attributes + ',', size_start + 1) - (size_start + 1))) AS size_string
FROM ( VALUES('Something = Something,Else = Else,blah=blah...,Size = XXL ,Fits to Chest Size = 48 to 50 in,,Closure Type = Snap Button ,Material = Cotton ,Color = Khaki ,Sleeve Length = 33 in,Lining Material = Polyester Thread ,,Fabric Weight = 9 oz,,,Resists = Flame') ) AS test_data(Attributes)
CROSS APPLY (
    SELECT CHARINDEX(',Size =', Attributes) AS size_start
) AS ca1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".