June 22, 2016 at 8:13 am
I have nvarchar data that looks like XXXXXX-XX-XX where X can be a character or a number. The problem is getting them to sort in a meaningful manner. For example, if I have the following test data...
DECLARE @JobNum TABLE([JobNum] [NVARCHAR](14) NOT NULL);
INSERT INTO @JobNum
(JobNum)
VALUES
('ABLobby-ABL-1')
,('ABLobby-ABL-2')
,('ABLobby-ABL-5')
,('ABLobby-ABL-10')
,('ABLobby-ABL-11')
,('ABLobby-ABL-15')
,('ABLobby-ABL-20')
,('001656-1-1')
,('001656-2-1')
,('001656-10-1')
,('001656-13-1')
,('001656-13-2')
,('001656-13-10')
,('001656-13-11')
I get the following if I order by JobNum...
(No column name)JobNum
@JobNum001656-10-1
@JobNum001656-1-1
@JobNum001656-13-1
@JobNum001656-13-10
@JobNum001656-13-11
@JobNum001656-13-2
@JobNum001656-2-1
@JobNumHPLobby-HPL-1
@JobNumHPLobby-HPL-10
@JobNumHPLobby-HPL-11
@JobNumHPLobby-HPL-15
@JobNumHPLobby-HPL-2
@JobNumHPLobby-HPL-20
@JobNumHPLobby-HPL-5
I have what I think is a pretty cool solution using two CROSS APPLY statements and it works great.
SELECT JobNum.JobNum
, SortJobNum
FROM @JobNum AS JobNum
CROSS APPLY (SELECT CASE WHEN CHARINDEX('-',JobNum.JobNum,CHARINDEX('-',JobNum.JobNum)+1) - CHARINDEX('-',JobNum.JobNum) = 2
THEN STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000')
WHEN CHARINDEX('-',JobNum.JobNum,CHARINDEX('-',JobNum.JobNum)+1) - CHARINDEX('-',JobNum.JobNum) = 3
THEN STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00')
WHEN CHARINDEX('-',JobNum.JobNum,CHARINDEX('-',JobNum.JobNum)+1) - CHARINDEX('-',JobNum.JobNum) = 4
THEN STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0')
ELSE JobNum.JobNum
END AS zJobNum
) _CA1
CROSS APPLY (SELECT CASE WHEN LEN(zJobNum)-CHARINDEX('-',zJobNum,LEN(zJobNum)-5) = 1 THEN STUFF(zJobNum,CHARINDEX('-',zJobNum,LEN(zJobNum)-5),1,'-000')
WHEN LEN(zJobNum)-CHARINDEX('-',zJobNum,LEN(zJobNum)-5) = 2 THEN STUFF(zJobNum,CHARINDEX('-',zJobNum,LEN(zJobNum)-5),1,'-00')
WHEN LEN(zJobNum)-CHARINDEX('-',zJobNum,LEN(zJobNum)-5) = 3 THEN STUFF(zJobNum,CHARINDEX('-',zJobNum,LEN(zJobNum)-5),1,'-0')
ELSE JobNum.JobNum
END AS SortJobNum
) _CA2
ORDER BY SortJobNum
With result..
JobNumSortJobNum
001656-1-1001656-0001-0001
001656-2-1001656-0002-0001
001656-10-1001656-0010-0001
001656-13-1001656-0013-0001
001656-13-2001656-0013-0002
001656-13-10001656-0013-0010
001656-13-11001656-0013-0011
HPLobby-HPL-1HPLobby-0HPL-0001
HPLobby-HPL-2HPLobby-0HPL-0002
HPLobby-HPL-5HPLobby-0HPL-0005
HPLobby-HPL-10HPLobby-0HPL-0010
HPLobby-HPL-11HPLobby-0HPL-0011
HPLobby-HPL-15HPLobby-0HPL-0015
HPLobby-HPL-20HPLobby-0HPL-0020
The problem is I need to move the query into our business system and it does not allow a CROSS APPLY, nor can I use a CTE so basically I need to perform to STUFF operations based on two different criteria in a single CASE statement. Or is there a better way? I also can't perform a SELECT statement as a line in a query. Screwy, I know, but those are my limitations.
Any help will be greatly appreciated.
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.
June 22, 2016 at 8:35 am
You have strings larger than the length of your column. Is that correct?
Even after formatting, you have different lengths on each section. Do you have a predefined length or will it be variable?
Do you have periods on you data? If not, there might be a nice way to get this.
June 22, 2016 at 8:39 am
The result will be used in a query for sorting so the length will not matter. If I need to store it in a temporary table I define it as [NVARCHAR](25).
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.
June 22, 2016 at 8:45 am
Here are 2 options that might help you depending on your data.
SELECT *,
PARSENAME( REPLACE(JobNum, '-', '.'), 3) + '-'
+ REPLICATE( '0', 4 - LEN(PARSENAME( REPLACE(JobNum, '-', '.'), 2))) + PARSENAME( REPLACE(JobNum, '-', '.'), 2) + '-'
+ REPLICATE( '0', 4 - LEN(PARSENAME( REPLACE(JobNum, '-', '.'), 1))) + PARSENAME( REPLACE(JobNum, '-', '.'), 1) WithoutPeriods,
REPLACE( PARSENAME( REPLACE(REPLACE(JobNum, '.', CHAR(7)), '-', '.'), 3) + '-'
+ REPLICATE( '0', 4 - LEN(PARSENAME( REPLACE(REPLACE(JobNum, '.', CHAR(7)), '-', '.'), 2))) + PARSENAME( REPLACE(REPLACE(JobNum, '.', CHAR(7)), '-', '.'), 2) + '-'
+ REPLICATE( '0', 4 - LEN(PARSENAME( REPLACE(REPLACE(JobNum, '.', CHAR(7)), '-', '.'), 1))) + PARSENAME( REPLACE(REPLACE(JobNum, '.', CHAR(7)), '-', '.'), 1), CHAR(7), '.') WithPeriods
FROM @JobNum
June 22, 2016 at 8:48 am
I got it, but it is ugly...
SELECT 'STUFF 2'
, JobNum.JobNum
, CASE WHEN CHARINDEX('-',JobNum.JobNum,CHARINDEX('-',JobNum.JobNum)+1) - CHARINDEX('-',JobNum.JobNum) = 2
THEN CASE WHEN LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000'))
-CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000'))-5) = 1
THEN STUFF(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000'),
CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000'))-5),1,'-000')
WHEN LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000'))
-CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000'))-5) = 2
THEN STUFF(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000'),
CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000'))-5),1,'-00')
WHEN LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000'))
-CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000'))-5) = 3
THEN STUFF(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000'),
CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-000'))-5),1,'-0')
END
WHEN CHARINDEX('-',JobNum.JobNum,CHARINDEX('-',JobNum.JobNum)+1) - CHARINDEX('-',JobNum.JobNum) = 3
THEN CASE WHEN LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00'))
-CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00'))-5) = 1
THEN STUFF(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00'),
CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00'))-5),1,'-000')
WHEN LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00'))
-CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00'))-5) = 2
THEN STUFF(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00'),
CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00'))-5),1,'-00')
WHEN LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00'))
-CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00'))-5) = 3
THEN STUFF(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00'),
CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-00'))-5),1,'-0')
END
WHEN CHARINDEX('-',JobNum.JobNum,CHARINDEX('-',JobNum.JobNum)+1) - CHARINDEX('-',JobNum.JobNum) = 4
THEN CASE WHEN LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0'))
-CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0'))-5) = 1
THEN STUFF(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0'),
CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0'))-5),1,'-000')
WHEN LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0'))
-CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0'))-5) = 2
THEN STUFF(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0'),
CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0'))-5),1,'-00')
WHEN LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0'))
-CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'0'))-5) = 3
THEN STUFF(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0'),
CHARINDEX('-',STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0')
,LEN(STUFF(JobNum.JobNum,CHARINDEX('-',JobNum.JobNum),1,'-0'))-5),1,'-0')
END
ELSE JobNum.JobNum
END AS SortJobNum
FROM @JobNum AS JobNum
ORDER BY SortJobNum
results in ...
JobNum SortJobNum
STUFF 2001656-1-1 001656-0001-0001
STUFF 2001656-2-1 001656-0002-0001
STUFF 2001656-10-1001656-0010-0001
STUFF 2001656-13-1001656-0013-0001
STUFF 2001656-13-2001656-0013-0002
STUFF 2001656-13-10001656-0013-0010
STUFF 2001656-13-11001656-0013-0011
STUFF 2ABLobby-ABL-1ABLobby-0ABL-0001
STUFF 2ABLobby-ABL-2ABLobby-0ABL-0002
STUFF 2ABLobby-ABL-5ABLobby-0ABL-0005
STUFF 2ABLobby-ABL-10ABLobby-0ABL-0010
STUFF 2ABLobby-ABL-11ABLobby-0ABL-0011
STUFF 2ABLobby-ABL-15ABLobby-0ABL-0015
STUFF 2ABLobby-ABL-20ABLobby-0ABL-0020
I would still like to know if there is a better way to do this. Oh, and I can't use a function either. Our business system has an interface that builds the query and they only programmed in the basic commands. So, no I don't like it but I am stuck with it.
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.
June 22, 2016 at 8:55 am
Look up PARSENAME as Luis posted. Ideal for this.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 22, 2016 at 8:59 am
DECLARE @JobNum TABLE([JobNum] [NVARCHAR](14) NOT NULL);
INSERT INTO @JobNum (JobNum) VALUES
('ABLobby-ABL-1')
,('ABLobby-ABL-2')
,('ABLobby-ABL-5')
,('ABLobby-ABL-10')
,('ABLobby-ABL-11')
,('ABLobby-ABL-15')
,('ABLobby-ABL-20')
,('001656-1-1')
,('001656-2-1')
,('001656-10-1')
,('001656-13-1')
,('001656-13-2')
,('001656-13-10')
,('001656-13-11')
SELECT *,
PARSENAME(REPLACE(JobNum,'-','.'),3),
PARSENAME(REPLACE(JobNum,'-','.'),2) ,
PARSENAME(REPLACE(JobNum,'-','.'),1)
FROM @JobNum
ORDER BY
PARSENAME(REPLACE(JobNum,'-','.'),3),
PARSENAME(REPLACE(JobNum,'-','.'),2) ,
PARSENAME(REPLACE(JobNum,'-','.'),1)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 22, 2016 at 9:05 am
That looks so much better than the ugly CASE. Looks like it is time for me to lookup PARSENAME and REPLICATE. If I ever learned them, I forgot and have not used them. Excellent, new knowledge. Thanks.
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply