Click here to Skip to main content
14,364,335 members
   

Database

 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 20:29
protectorJörgen Andersson2-Sep-19 20:29 
AnswerMessage Closed Pin
16-Sep-19 21:25
membergentorify16-Sep-19 21:25 
QuestionAdvice or opinion on database planning for Angular using MongoDB and perhaps a 2nd technology. Pin
jkirkerx30-Aug-19 9:16
professionaljkirkerx30-Aug-19 9:16 
AnswerRe: Advice or opinion on database planning for Angular using MongoDB and perhaps a 2nd technology. Pin
Afzaal Ahmad Zeeshan30-Aug-19 12:56
mveAfzaal Ahmad Zeeshan30-Aug-19 12:56 
GeneralRe: Advice or opinion on database planning for Angular using MongoDB and perhaps a 2nd technology. Pin
jkirkerx1-Sep-19 11:46
professionaljkirkerx1-Sep-19 11:46 
QuestionCompact and Repair Access Database using C# ? Pin
Member 245846729-Aug-19 16:14
memberMember 245846729-Aug-19 16:14 
AnswerRe: Compact and Repair Access Database using C# ? Pin
Victor Nijegorodov29-Aug-19 21:37
memberVictor Nijegorodov29-Aug-19 21:37 
QuestionHwere's a Head-Scratcher Pin
#realJSOP28-Aug-19 10:37
mve#realJSOP28-Aug-19 10:37 
Using SQL Server 2016 - I have a table with a TREE column (type is varchar) with data such as the following:

1.0
1.0.1
1.0.2
1.0.2.1
1.0.2.2
1.10.1.35

0) The number of "octets" (values between the periods) is an unknown quantity (it could be anywhere from 2 to 10 levels deep).

2) The number of digits within a given octet will be at least one, but never more than 2.

Desired output: I want all single-digit octets to be 0-padded. So given the sample above, the output would look like this:

01.00
01.00.01
01.00.02
01.00.02.01
01.00.02.02
01.10.01.35

I did it like this, and after spending some time with a couple of DBA's they couldn't improve on it:

;WITH cte AS
(
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('.'+TREE+'.',
    '.0.', '.00.'),
    '.1.', '.01.'),
    '.2.', '.02.'),
    '.3.', '.03.'),
    '.4.', '.04.'),
    '.5.', '.05.'),
    '.6.', '.06.'),
    '.7.', '.07.'),
    '.8.', '.08.'),
    '.9.', '.09.') AS TREE
    FROM MYTABLE
)
, cte2 AS
(
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('.'+TREE+'.',
    '.0.', '.00.'),
    '.1.', '.01.'),
    '.2.', '.02.'),
    '.3.', '.03.'),
    '.4.', '.04.'),
    '.5.', '.05.'),
    '.6.', '.06.'),
    '.7.', '.07.'),
    '.8.', '.08.'),
    '.9.', '.09.') AS TREE
    FROM cte
)
SELECT * FROM cte2;


Is there a better way?
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013

AnswerRe: Hwere's a Head-Scratcher Pin
Richard Deeming28-Aug-19 11:00
mveRichard Deeming28-Aug-19 11:00 
GeneralRe: Hwere's a Head-Scratcher Pin
Mycroft Holmes28-Aug-19 13:42
memberMycroft Holmes28-Aug-19 13:42 
GeneralRe: Hwere's a Head-Scratcher Pin
#realJSOP29-Aug-19 0:23
mve#realJSOP29-Aug-19 0:23 
GeneralRe: Hwere's a Head-Scratcher Pin
Richard Deeming29-Aug-19 2:22
mveRichard Deeming29-Aug-19 2:22 
GeneralRe: Hwere's a Head-Scratcher Pin
#realJSOP29-Aug-19 2:47
mve#realJSOP29-Aug-19 2:47 
GeneralRe: Hwere's a Head-Scratcher Pin
Richard Deeming29-Aug-19 2:55
mveRichard Deeming29-Aug-19 2:55 
GeneralRe: Hwere's a Head-Scratcher Pin
#realJSOP29-Aug-19 2:59
mve#realJSOP29-Aug-19 2:59 
QuestionError With Simple Script Pin
Kevin Marois22-Aug-19 9:26
professionalKevin Marois22-Aug-19 9:26 
AnswerRe: Error With Simple Script Pin
Richard Deeming23-Aug-19 2:08
mveRichard Deeming23-Aug-19 2:08 
GeneralRe: Error With Simple Script Pin
Kevin Marois23-Aug-19 7:21
professionalKevin Marois23-Aug-19 7:21 
QuestionError With Simple Script Pin
Kevin Marois22-Aug-19 9:25
professionalKevin Marois22-Aug-19 9:25 
AnswerRe: Error With Simple Script Pin
David Mujica24-Oct-19 10:56
memberDavid Mujica24-Oct-19 10:56 
QuestionAccess SQL Server Remotely Via VPN Pin
Kevin Marois20-Aug-19 6:40
professionalKevin Marois20-Aug-19 6:40 
AnswerRe: Access SQL Server Remotely Via VPN Pin
Richard Deeming20-Aug-19 6:56
mveRichard Deeming20-Aug-19 6:56 
QuestionSQL Remote Connection Problem Pin
Kevin Marois19-Aug-19 8:52
professionalKevin Marois19-Aug-19 8:52 
AnswerRe: SQL Remote Connection Problem Pin
Richard Deeming19-Aug-19 9:45
mveRichard Deeming19-Aug-19 9:45 
GeneralRe: SQL Remote Connection Problem Pin
Kevin Marois19-Aug-19 10:21
professionalKevin Marois19-Aug-19 10:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.