Jeff Moden

Forum Replies Created

Viewing 15 posts - 7,366 through 7,380 (of 59,072 total)

Reply To: pulling multiple numbers out of a string

Jonathan AC Roberts wrote:
Is it really SQL 7, 2000 you are using?
@tpd1989 ,
This is the KEY question on this thread. Please confirm that you are actually using SQL 7 or 2000 or...
--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)

April 5, 2020 at 2:38 pm

#3740562
Reply To: pulling multiple numbers out of a string

@bill,
Neither CROSS APPLY nor the multiline version of VALUES is available in 7/2000.

--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)

April 5, 2020 at 2:33 pm

#3740560
Reply To: Need T-SQL to list all tables in database that 'qualify' for Compression

VoldemarG wrote:
THANKS!
Question: can there be any reason that an error will be thrown while attempting to compress the table? Like what should be 'wrong' with table to cause the compression...
--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)

April 5, 2020 at 1:07 pm

#3740548
Reply To: expand field and fill with zeros

Jeffrey Williams wrote:
We might even be able to save those 4 extra characters - depending on the source data. If we know for sure that we have a minimum length 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)

April 4, 2020 at 9:38 pm

#3740484
Reply To: expand field and fill with zeros

Sounds good. Looking forward to it.

--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)

April 4, 2020 at 9:12 pm

#3740483
Reply To: Why would I want to use Compress and Decompress functions?

It's also great for the archival of data. Please see "Example B" at the following link.
https://docs.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql?view=sql-server-ver15
It could also be an easy way to ZIP such data for data transmission...
--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)

April 4, 2020 at 8:03 pm

#3740480
Reply To: Updating data with recording old values - thought process

This is a nice narrow table. You can do a lot of what you ask for auto-magically in 2017 using System-Versioned Temporal Tables. It will not only record the history...
--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)

April 4, 2020 at 7:51 pm

#3740479
Reply To: expand field and fill with zeros
Steve Collins wrote:
For my part I really appreciate the deep analysis into components. It's vital to make progress in a good way so I think it's essential research. It NEVER takes...
- This reply was modified 6 years, 3 months ago by Jeff Moden.
--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)
April 4, 2020 at 7:37 pm

#3740474
Reply To: expand field and fill with zeros
Jeffrey Williams wrote:
Just one point - not sure it makes a difference in performance but you can avoid this:
```
@BitBucket = RIGHT('00000000000000'+CONVERT(VARCHAR(14),Col01),14)
```
By using this:
```
@BitBucket = RIGHT(CONCAT('00000000000000',...
```
--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)
April 4, 2020 at 7:15 pm

#3740473
Reply To: Replacing / Removing All Text In Multiple Sets of Characters in SQL
IF the pattern is consistent, this is easy.
```
--===== Create the test data.  This is not a part of the solution.
 CREATE TABLE #Temp
  ...
```
--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)
April 4, 2020 at 6:19 pm

#3740460
Reply To: Identity column as Primary Key - good or bad?

jcelko212 32090 wrote:
>> THIS coming from the person that says he likes the MySQL "standard" of using YYYY-00-00 as the notation for whole years and YYYY-MM-00 for whole months. What a...
--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)

April 4, 2020 at 5:10 pm

#3740451
Reply To: expand field and fill with zeros

Steve Collins wrote:
Jeff Moden wrote:
Steve Collins wrote:
Jeff Moden wrote:
Lordy, no. That's one of the slowest methods possible. I'll be back to prove it. Just don't use FORMAT!
Jeff, maybe so, maybe so. It appears the...
--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)

April 4, 2020 at 4:22 pm

#3740445
Reply To: expand field and fill with zeros

jcelko212 32090 wrote:
From what you've posted, you start off wanting to treat this data element as a string, but then you're storing it as a numeric. You also don't know that...
--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)

April 4, 2020 at 2:55 am

#3740367
Reply To: Basic SQL Question

pietlinden wrote:
"Pfffft! *he* wrote it??? Not reading that!!"
LOL
What I meant was that you posted the correct answer first and I missed that. 😀
--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)

April 4, 2020 at 2:34 am

#3740366
Reply To: Basic SQL Question

Thanks for the feedback but I'm, actually pretty late with my observation. pietlinden pretty much said in his first post what I was talking about and I simply didn't read...
--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)

April 4, 2020 at 12:37 am

#3740361

Viewing 15 posts - 7,366 through 7,380 (of 59,072 total)