Jeff Moden

Forum Replies Created

Viewing 15 posts - 22,711 through 22,725 (of 59,102 total)

RE: Remove non alpha chars from a column

dwain.c (9/29/2014)
Jeff,
You are correct. Adding that TYPE/value to the FOR XML is going to kill the performance of the string cleaner I posted. Which of course, I mistakenly...
--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)

September 29, 2014 at 5:57 am

#1748773
RE: An Alternative (Better?) Method to UNPIVOT (SQL Spackle)

dwain.c (9/28/2014)
Stuart Pearson (9/26/2014)
Hi, I just successfully ran this in SQL 2012 using a database at compatibility level 80!!
Interesting. I wouldn't have thought that would be possible.
What would be...
--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)

September 28, 2014 at 7:37 pm

#1748699
RE: How to get ssns that occures in less than 12 month gap

Why do people insist that it's ok to store SSNs in plain text?
--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)

September 28, 2014 at 5:45 pm

#1748692
RE: Linked Server to AS400 Error Null Value

Welsh Corgi (9/26/2014)
I have a Stored Procedure that uses an Open Query to load a SQL Server Table.
This is the Open Query part:
SELECT *
...
--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)

September 28, 2014 at 12:02 pm

#1748678
RE: Select case statement

glarry (9/28/2014)
Is it possible to use the result of case statement as Alias?
Select date, case when age>20 then age End as Test+Age value
No. Not unless you have some form...
--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)

September 28, 2014 at 11:33 am

#1748677
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

corey lawson (9/26/2014)
I came across the Quirky Update initially just last week in one of Phil Factor's "Speed Phreak" challenges, where in the lengthy commentary regarding posted candidate solutions, someone...
--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)

September 27, 2014 at 3:29 pm

#1748644
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

WayneS (5/28/2014)
autoexcrement (5/28/2014)
Has anyone done any significant testing of the Quirky Update method in SQL Server 2008, 2012, or 2014? Does it work in those versions too?
It's easy enough...
--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)

September 27, 2014 at 3:16 pm

#1748643
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Seggerman-675349 (3/14/2014)
Jeff - on behalf of my client 😛
see http://www.sqlservercentral.com/Forums/Topic1550386-392-1.aspx
I took a stored procedure, modified (but not enough) which had used cursors in Oracle that ran for 88 minutes 2...
--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)

September 27, 2014 at 3:08 pm

#1748642
RE: Remove non alpha chars from a column

Here's the testing I did on this...
First, create the two original functions with no modifications, Bob's CleanString_bh and my CleanString.
CREATE FUNCTION dbo.CleanString_bh
...
--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)

September 27, 2014 at 1:22 pm

#1748641
RE: Remove non alpha chars from a column

dwain.c (3/4/2014)
The Dixie Flatline (3/4/2014)
I'm with Dwain on this one.
CREATE FUNCTION dbo.CleanString_bh
(@pString VARCHAR(8000),@pPattern VARCHAR(100))
RETURNS TABLE
AS
RETURN
(
with tally as (select top(len(@pstring)) N...
--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)

September 26, 2014 at 10:22 pm

#1748628
RE: Random values

Steve Jones - SSC Editor (9/25/2014)
edwardwill (9/25/2014)
Can you explain why you aliased the sys.syscolumns table?
SELECT TOP 10
RAND( ROW_NUMBER() OVER (ORDER BY id))
FROM sys.syscolumns
is functionally identical, as...
--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)

September 26, 2014 at 5:06 pm

#1748622
RE: Looking For third party Tools.....

Jerry Inman (9/26/2014)
I used RedGate at a previous job (Kennedy Space Center), but chose Idera products for my current place of employment (Healthcare company). Price and features were the primary...
--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)

September 26, 2014 at 3:19 pm

#1748616
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

stefan.mulder (9/24/2014)
Great stuff!
but as to Jeff's notes on collation: we do need a function that can handle compound delimiters, e.g. [|;] or [","] as in David Data's case of...
--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)

September 26, 2014 at 12:18 am

#1748384
RE: Another Way to Look at Trees

fregatepllada (9/24/2014)
Jeff - from business prospective I have to deal with situations when a Customer of on-line Assessment system could bring their own definition of their curriculum (and trust me...
--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)

September 25, 2014 at 11:37 am

#1748267
RE: Export to CSV and email

You're a man after my own heart, Nadrek. I had a fairly major project that I solved using copies of SQL Express setup as "bricks" to do nothing but...
--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)

September 25, 2014 at 8:51 am

#1748207

Viewing 15 posts - 22,711 through 22,725 (of 59,102 total)