Extract Substring from a string using SQL

Question

Body
Beta M yy-Tz
Beta M zz-Ox
Beta W yy-Tz
Tiger W Dash
Tiger M Dash
Tiger White Stone W Pearl
Tiger White Stone M Pearl

I have a column 'Body' in a snowflake table xyz which contains the products being ordered. The 'Body' field is composed of three parts.
1st part: Product Name (Ex: Beta, Tiger, Tiger White Stone)

2nd part: Gender M or W and

3rd part is internal product naming (Ex: yy | Tz, zz | Ox, Dash and Pearl).

I want to extract the M and W in the separate column named Gender to know how many men and women customers I have.

Expected output:

Gender
M
M
W
W
M
W
M

I tried to use the split function but I feel it is not an efficient way. Is there a better way to get this implemented?

I have rolled back your last edit, as it added tag spam. Tag spam doesn't help us help you; it makes it harder to. Tagging multiple conflicting tags means that we have no idea what technology you are really asking about, making your question unclear and difficult to answer. It can also easily end up attracting downvotes and also close votes if they make the question unclear. Just tag the technologies you are actually asking about. If you aren't using snowflake (your closing sentence said you are, so why tag those other prodcuts?), remove that tag and add the correct RDBMS tag. — Thom A
– Thom A ♦, Commented Jun 14, 2022 at 11:39

NickW · Accepted Answer · 2022-06-14 10:00:09Z

2

Because the "parts" of the string can contain spaces then using spaces to find the 2nd part is probably not reliable. I'm going to assume that the patterns you are looking for are ' M ' and ' W ' but obviously if these patterns can exist in the first or third part this won't work.

Try this:

CASE WHEN POSITION(' M ', BODY) > 0 THEN 'M'
     WHEN POSITION(' W ', BODY) > 0 THEN 'W'
     ELSE 'X' END

answered Jun 14, 2022 at 10:00

NickW

10k2 gold badges10 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kushal Over a year ago

This works perfect but this will not be an efficient way if my conditions (Ex: M, W, X, U....etc) increases down the line. can it be solved using Regex?

NickW Over a year ago

Only if there is a consistent pattern to match against. If you can guarantee that the second to last word will always be the one you want to extract then you could use regex. However, if the 3rd part could consist of 1, 2 or more words (and this changes from record to record) then you couldn't use regex as you would have no pattern to match against

Collectives™ on Stack Overflow

Extract Substring from a string using SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related