September 27, 2019 at 9:15 pm
Hello community,
I have a little challenge to accomplish, but i need your help.
i have a varchar field that is the description of my product like these 2 ones:
Ref Design
2566000 Sandália BEBE 19/24 Cx12
2578995 Chinelo M/ Dedo SENHORA 35/40 Cx12
I need to keep only the 2 first character before / and also the 2 last character after /.
in first line i want to keep in column name [FirstChar] 19 and in another column [LastChar] 24
in second line i want to keep in column name [FirstChar] 35 and in another column [LastChar] 40
My first problem is in some case i have 2 times the character /
Also, if someone could help me to solve this issue, i want to create a sequence between [FirstChar] and [LastChar] for each ref like this:
2566000 19
2566000 20
2566000 21
2566000 22
2566000 23
2566000 24
2578995 35
2578995 36
2578995 37
2578995 38
2578995 39
2578995 40
Could someone help me to solve my issue ?
Many thanks and best regards,
Luis
September 27, 2019 at 9:52 pm
Here is a start for you. I don't have time to complete part 2 right now.
DROP TABLE IF EXISTS #data;
CREATE TABLE #data
(
Ref INT
,Design VARCHAR(200)
);
INSERT #data
(
Ref
,Design
)
VALUES
(2566000, 'Sandália BEBE 19/24 Cx12')
,(2578995, 'Chinelo M/ Dedo SENHORA 35/40 Cx12');
SELECT d.Ref
,Num1 = CAST(SUBSTRING(d.Design, pos.StartPos, 2) AS INT)
,Num2 = CAST(SUBSTRING(d.Design, pos.StartPos + 3, 2) AS INT)
FROM #data d
CROSS APPLY
(SELECT StartPos = PATINDEX('%[0-9][0-9]/[0-9][0-9]%', d.Design)) pos;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 27, 2019 at 9:57 pm
Hello Phil,
thank you very much for the reply and thank you when you have a little time, if you can help me solve the second part because this will be very important for me to be able to solve my problem.
best regards
Luis
September 28, 2019 at 5:29 pm
Hello community,
Phil send me this excellent code and I solve Part1.
but on Part 2 of my question i want to create a sequence between [FirstChar] and [LastChar] for each ref like my example.
I need this to create an Insert statment:
2566000 19
2566000 20
2566000 21
2566000 22
2566000 23
2566000 24
2578995 35
2578995 36
2578995 37
2578995 38
2578995 39
2578995 40
I try to find something on Google but without sucess.
Please give me a solution to do this.
Best regards,
Luis
September 28, 2019 at 6:18 pm
Try this
DROP TABLE IF EXISTS #data;
CREATE TABLE #data
(
Ref INT
,Design VARCHAR(200)
);
INSERT #data
(
Ref
,Design
)
VALUES
(2566000, 'Sandália BEBE 19/24 Cx12')
,(2578995, 'Chinelo M/ Dedo SENHORA 35/40 Cx12');
WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0)
,lv1 AS (SELECT 0 g UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 FROM lv0 a CROSS JOIN lv0 b)
,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv1)
SELECT d.Ref
,t.n
FROM #data d
CROSS APPLY
(SELECT StartPos = PATINDEX('%[0-9][0-9]/[0-9][0-9]%', d.Design)) pos
CROSS APPLY
(
SELECT Num1 = CAST(SUBSTRING(d.Design, pos.StartPos, 2) AS INT)
,Num2 = CAST(SUBSTRING(d.Design, pos.StartPos + 3, 2) AS INT)
) Nums
CROSS JOIN Tally t
WHERE t.n
BETWEEN Nums.Num1 AND Nums.Num2;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 28, 2019 at 10:45 pm
Phil's code works great for this but I do have a question...
Is there any chance of having less or more than 2 digits on either side of the "/" ?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 29, 2019 at 1:03 pm
hello Phil
I have no words to thank you, without your help it would have taken too long time to solve this challenge of mine.
Thank you so much for your great help.
Best regards,
Luis
September 29, 2019 at 1:12 pm
Hello Jeff,
Thanks you to reply to my post.
In this case i have always 2 digits on either side, but thanks for asking if I will always have 2 digits before and after the '/' symbol.
Assuming it would only have 1 digits before or after the '/' symbol, how could you solve this question using the example sent by Phil.
Many thanks,
Best regards,
Luis
September 30, 2019 at 12:21 am
Changing Phil's code to handle single digits is easy because of the nature of numeric strings, if your pattern is guaranteed. Except for the comments, the additional test data, and the ORDER BY in the following, I only added two spaces to Phil's good code to handle either 1 or 2 digits on either or both sides of the '/'.
DROP TABLE IF EXISTS #data;
CREATE TABLE #data
(
Ref INT
,Design VARCHAR(200)
);
INSERT #data
(
Ref
,Design
)
VALUES
(2566000, 'Sandália BEBE 19/24 Cx12')
,(2578995, 'Chinelo M/ Dedo SENHORA 35/40 Cx12')
,(2578996, 'Chinelo M/ Dedo SENHORA 4/8 Cx12') --Changed this to test
,(2578997, 'Chinelo M/ Dedo SENHORA 6/12 Cx12') --Add this to test
;
WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0)
,lv1 AS (SELECT 0 g UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 FROM lv0 a CROSS JOIN lv0 b)
,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv1)
SELECT d.Ref
,t.n
FROM #data d
CROSS APPLY
(SELECT StartPos = PATINDEX('%[ 0-9][0-9]/[0-9][ 0-9]%', d.Design)) pos --Added space to first an last character of patterrn
CROSS APPLY
(
SELECT Num1 = CAST(SUBSTRING(d.Design, pos.StartPos, 2) AS INT)
,Num2 = CAST(SUBSTRING(d.Design, pos.StartPos + 3, 2) AS INT)
) Nums
CROSS JOIN Tally t
WHERE t.n BETWEEN Nums.Num1 AND Nums.Num2
ORDER BY Ref,N --Added this to test
;
It's not such an easy change if you need to be able to handle more than 2 digit numbers and that's what I was really asking about.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 30, 2019 at 4:40 am
To answer the question where the numbers could be anywhere from 1 to 10 digits (up to the max of INT), here's some test code (borrowing heavily on Phil's good test code) to demonstrate...
--===== Create and populate the test table.
DROP TABLE IF EXISTS #Data
;
CREATE TABLE #Data
(
Ref INT
,Design VARCHAR(200)
)
;
INSERT INTO #Data
(Ref,Design)
VALUES (2566000, 'Sandália BEBE 19/24 Cx12')
,(2578995, 'Chinelo M/ Dedo SENHORA 35/40 Cx12')
,(2578996, 'Chinelo M/ Dedo SENHORA 4/8 Cx12') --Changed this to test
,(2578997, 'Chinelo M/ Dedo SENHORA 6/12 Cx12') --Add this to test
,(2578998, 'Chinelo M/ Dedo SENHORA a780/785 Cx12') --This needs to be rejected
,(2578999, 'Chinelo M/ Dedo SENHORA a780/785 Cx12') --This needs to be rejected
,(2579000, 'Chinelo M/ Dedo SENHORA 780/7a85 Cx12') --This needs to be rejected
,(2579000, 'Chinelo M/ Dedo SENHORA 780/785 Cx12') --This will also be processed
,(2579000, 'Chinelo M/ Dedo SENHORA 1200380100/1200380105 Cx12') --This will also be processed
;
... and here's one possible solution to pull it off...
--===== Solve the probleem
WITH cteParse AS
(--==== Find and parse the pattern we''re after (up to max value of int on either side of the '/').
SELECT d.Ref
,LoNum = SUBSTRING(s.Item,1,CHARINDEX('/',s.Item)-1)+0
,HiNum = SUBSTRING(s.Item,CHARINDEX('/',s.Item)+1,21)+0
FROM #Data d
CROSS APPLY dbo.DelimitedSplit8K(d.Design,' ') s --Split on spaces in the string
WHERE d.Design LIKE '%[0-9]/[0-9]%' --Short cut to reject prior to splitting
AND s.Item LIKE '%[0-9]/[0-9]%'
AND s.Item NOT LIKE '%[^0-9/]%'
)--==== Use the Tally function to do the relational multiplication of numbered rows.
SELECT p.Ref
,N = t.N + p.LoNum
FROM cteParse p
CROSS APPLY dbo.fnTally(0,p.HiNum-p.LoNum) t
;
You can get the fnTally function from the like-named link in my signature line below. And, here's my latest version of the DelimitedSplit8K function built for use in SQL Server 2012 and up. It includes the performance optimization that used LEAD by Eirikur Eiriksson and a little extra boost for odd collations by using a binary collation, which also makes it all case sensitive when it comes to delimiters.
CREATE FUNCTION dbo.DelimitedSplit8K
/**********************************************************************************************************************
Purpose:
Split a given string at a given delimiter and return a list of the split elements (items).
Notes:
1. Leading and trailing delimiters are treated as if an empty string element were present.
2. Consecutive delimiters are treated as if an empty string element were present between them.
3. Except when spaces are used as a delimiter, all spaces present in each element are preserved.
Returns:
iTVF containing the following:
ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST)
Item = Element value as a VARCHAR(8000)
Note that this function uses a binary collation and is, therefore, case sensitive.
The original article for the concept of this splitter may be found at the following URL. You can also find
performance tests at this link although they are now a bit out of date. This function is much faster as of Rev 09,
which was built specifically for use in SQL Server 2012 and above andd is about twice as fast as the version
document in the article.
http://www.sqlservercentral.com/Forums/Topic1101315-203-4.aspx
-----------------------------------------------------------------------------------------------------------------------
CROSS APPLY Usage Examples and Tests:
--=====================================================================================================================
-- TEST 1:
-- This tests for various possible conditions in a string using a comma as the delimiter. The expected results are
-- laid out in the comments
--=====================================================================================================================
--===== Conditionally drop the test tables to make reruns easier for testing.
-- (this is NOT a part of the solution)
IF OBJECT_ID('tempdb..#JBMTest') IS NOT NULL DROP TABLE #JBMTest
;
--===== Create and populate a test table on the fly (this is NOT a part of the solution).
-- In the following comments, "b" is a blank and "E" is an element in the left to right order.
-- Double Quotes are used to encapsulate the output of "Item" so that you can see that all blanks
-- are preserved no matter where they may appear.
SELECT *
INTO #JBMTest
FROM ( --# of returns & type of Return Row(s)
SELECT 0, NULL UNION ALL --1 NULL
SELECT 1, SPACE(0) UNION ALL --1 b (Empty String)
SELECT 2, SPACE(1) UNION ALL --1 b (1 space)
SELECT 3, SPACE(5) UNION ALL --1 b (5 spaces)
SELECT 4, ',' UNION ALL --2 b b (both are empty strings)
SELECT 5, '55555' UNION ALL --1 E
SELECT 6, ',55555' UNION ALL --2 b E
SELECT 7, ',55555,' UNION ALL --3 b E b
SELECT 8, '55555,' UNION ALL --2 b B
SELECT 9, '55555,1' UNION ALL --2 E E
SELECT 10, '1,55555' UNION ALL --2 E E
SELECT 11, '55555,4444,333,22,1' UNION ALL --5 E E E E E
SELECT 12, '55555,4444,,333,22,1' UNION ALL --6 E E b E E E
SELECT 13, ',55555,4444,,333,22,1,' UNION ALL --8 b E E b E E E b
SELECT 14, ',55555,4444,,,333,22,1,' UNION ALL --9 b E E b b E E E b
SELECT 15, ' 4444,55555 ' UNION ALL --2 E (w/Leading Space) E (w/Trailing Space)
SELECT 16, 'This,is,a,test.' UNION ALL --4 E E E E
SELECT 17, ',,,,,,' --7 (All Empty Strings)
) d (SomeID, SomeValue)
;
--===== Split the CSV column for the whole table using CROSS APPLY (this is the solution)
SELECT test.SomeID, test.SomeValue, split.ItemNumber, Item = QUOTENAME(split.Item,'"')
FROM #JBMTest test
CROSS APPLY dbo.DelimitedSplit8K(test.SomeValue,',') split
;
--=====================================================================================================================
-- TEST 2:
-- This tests for various "alpha" splits and COLLATION using all ASCII characters from 0 to 255 as a delimiter against
-- a given string. Note that not all of the delimiters will be visible and some will show up as tiny squares because
-- they are "control" characters. More specifically, this test will show you what happens to various non-accented
-- letters for your given collation depending on the delimiter you chose.
--=====================================================================================================================
WITH
cteBuildAllCharacters (String,Delimiter) AS
(
SELECT TOP 256
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
CHAR(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)
FROM master.sys.all_columns
)
SELECT ASCII_Value = ASCII(c.Delimiter), c.Delimiter, split.ItemNumber, Item = QUOTENAME(split.Item,'"')
FROM cteBuildAllCharacters c
CROSS APPLY dbo.DelimitedSplit8K(c.String,c.Delimiter) split
ORDER BY ASCII_Value, split.ItemNumber
;
-----------------------------------------------------------------------------------------------------------------------
Other Notes:
1. Optimized for VARCHAR(8000) or less. No testing or error reporting for truncation at 8000 characters is done.
2. Optimized for single character delimiter. Multi-character delimiters should be resolved externally from this
function.
3. Optimized for use with CROSS APPLY.
4. Does not "trim" elements just in case leading or trailing blanks are intended.
5. If you don't know how a Tally table can be used to replace loops, please see the following...
http://www.sqlservercentral.com/articles/T-SQL/62867/
6. Changing this function to use a MAX datatype will cause it to run twice as slow. It's just the nature of
MAX datatypes whether it fits in-row or not.
-----------------------------------------------------------------------------------------------------------------------
Credits:
This code is the product of many people's efforts including but not limited to the folks listed in the Revision
History below:
I also thank whoever wrote the first article I ever saw on "numbers tables" which is located at the following URL
and to Adam Machanic for leading me to it many years ago. The link below no long works but has been preserved herer
for posterity sake.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
The original article can be seen at then following special site, as least as of 29 Sep 2019.
http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html#
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 20 Jan 2010 - Concept for inline cteTally: Itzik-Ben Gan, Lynn Pettis and others.
Redaction/Implementation: Jeff Moden
- Base 10 redaction and reduction for CTE. (Total rewrite)
Rev 01 - 13 Mar 2010 - Jeff Moden
- Removed one additional concatenation and one subtraction from the SUBSTRING in the SELECT List for that tiny
bit of extra speed.
Rev 02 - 14 Apr 2010 - Jeff Moden
- No code changes. Added CROSS APPLY usage example to the header, some additional credits, and extra
documentation.
Rev 03 - 18 Apr 2010 - Jeff Moden
- No code changes. Added notes 7, 8, and 9 about certain "optimizations" that don't actually work for this
type of function.
Rev 04 - 29 Jun 2010 - Jeff Moden
- Added WITH SCHEMABINDING thanks to a note by Paul White. This prevents an unnecessary "Table Spool" when the
function is used in an UPDATE statement even though the function makes no external references.
Rev 05 - 02 Apr 2011 - Jeff Moden
- Rewritten for extreme performance improvement especially for larger strings approaching the 8K boundary and
for strings that have wider elements. The redaction of this code involved removing ALL concatenation of
delimiters, optimization of the maximum "N" value by using TOP instead of including it in the WHERE clause,
and the reduction of all previous calculations (thanks to the switch to a "zero based" cteTally) to just one
instance of one add and one instance of a subtract. The length calculation for the final element (not
followed by a delimiter) in the string to be split has been greatly simplified by using the ISNULL/NULLIF
combination to determine when the CHARINDEX returned a 0 which indicates there are no more delimiters to be
had or to start with. Depending on the width of the elements, this code is between 4 and 8 times faster on a
single CPU box than the original code especially near the 8K boundary.
- Modified comments to include more sanity checks on the usage example, etc.
- Removed "other" notes 8 and 9 as they were no longer applicable.
Rev 06 - 12 Apr 2011 - Jeff Moden
- Based on a suggestion by Ron "Bitbucket" McCullough, additional test rows were added to the sample code and
the code was changed to encapsulate the output in pipes so that spaces and empty strings could be perceived
in the output. The first "Notes" section was added. Finally, an extra test was added to the comments above.
Rev 07 - 06 May 2011 - Peter de Heer
- A further 15-20% performance enhancement has been discovered and incorporated into this code which also
eliminated the need for a "zero" position in the cteTally table.
Rev 08 - 24 Mar 2014 - Eirikur Eiriksson
- Further performance modification (twice as fast) For SQL Server 2012 and greater by using LEAD to find the
next delimiter for the current element, which eliminates the need for CHARINDEX, which eliminates the need
for a second scan of the string being split.
REF: https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2
Rev 09 - 29 Sep 2019 - Jeff Moden
- Combine the improvements by Peter de Heer and Eirikur Eiriksson for use on SQL Server 2012 and above.
- Add Test 17 to the test code above.
- Modernize the generation of the embedded "Tally" generation available as of 2012. There's no significant
performance increase but it makes the code much shorter and easier to understand.
- Check/change all URLs in the notes abobe to ensure that they're still viable.
- Add a binary collation for a bit more of an edge on performance.
- Removed "Other Note" #7 above as UNPIVOT is no longern applicable (never was for performance).
**********************************************************************************************************************/
--=========== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--=========== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000, enough to cover VARCHAR(8000).
WITH E1(N) AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d)
,cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
,cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString COLLATE Latin1_General_BIN,t.N,1)
= @pDelimiter COLLATE Latin1_General_BIN
)
--=========== Do the actual split.
-- The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER (ORDER BY s.N1)
, Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1)-1),0)-s.N1,8000))
FROM cteStart s
;
--Jeff Moden
Change is inevitable... Change for the better is not.
September 30, 2019 at 4:24 pm
I have a VARCHAR(n) field [sic: columns are not fields!] that is the description of my product like these 2 ones:
Ref Design
2566000 Sandália BEBE 19/24 Cx12
2578995 Chinelo M/ Dedo SENHORA 35/40 Cx12
I need to keep only the 2 first character before / and also the 2 last character after /. <<
You don't understand one of the most fundamental concepts in SQL and RDBMS. It's called First Normal Form (1NF) and it states that a column (attribute) has to be a scalar. You've overloaded it with multiple meanings! Perhaps you would also like to put in the shoe and hat size of the man who created this product? See how silly that is? What you need to do is take the column and split out each atomic scalar value and put it in its own column, with its own name. You're trying to repair your bad design on the fly while executing a query.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 30, 2019 at 6:34 pm
I have a VARCHAR(n) field [sic: columns are not fields!] that is the description of my product like these 2 ones:
Ref Design
2566000 Sandália BEBE 19/24 Cx12
2578995 Chinelo M/ Dedo SENHORA 35/40 Cx12
I need to keep only the 2 first character before / and also the 2 last character after /. <<
You don't understand one of the most fundamental concepts in SQL and RDBMS. It's called First Normal Form (1NF) and it states that a column (attribute) has to be a scalar. You've overloaded it with multiple meanings! Perhaps you would also like to put in the shoe and hat size of the man who created this product? See how silly that is? What you need to do is take the column and split out each atomic scalar value and put it in its own column, with its own name. You're trying to repair your bad design on the fly while executing a query.
Consider for just a minute that the OP didn't actually create this mess. He's working with what he was given and trying to overcome the mess someone else made. He probably cannot make any changes to the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 30, 2019 at 11:03 pm
Hello Jeff,
Many thanks for your coding and comments on it.
I will go to make some test with it.
Many thanks,
Best regards,
Luis
October 1, 2019 at 9:12 pm
Thanks, Luis. I appreciate the feedback. If you get the chance, let us know how it all works out for you even if you don't use the code I posted (since it's not actually needed for your problem).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply