Jeff Moden

Forum Replies Created

Viewing 15 posts - 7,381 through 7,395 (of 59,091 total)

Reply To: pulling multiple numbers out of a string

tpd1989 wrote:
Hi Jeff, The second code option works exactly as I need it. I Saw Bill's first which also works so I marked that as the answer but wanted to...
--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 6:10 pm

#3740588
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 3:58 pm

#3740574
Reply To: Do people process data from beginning of time when there is data change?

frederico_fonseca wrote:
fransiscuscandra wrote:
Thank you for your feedback, appreciate your valuable time, skills and know ledges. Do you agree if we have more than 5 millions rows in source table, we...
--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 3:37 pm

#3740569
Reply To: pulling multiple numbers out of a string
Here's the test data using the "old fashioned" way that works in all versions of SQL Server since at least 6.5.
```
--===== Create the test table from what...
```
--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 3:28 pm

#3740566
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, 4 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

Viewing 15 posts - 7,381 through 7,395 (of 59,091 total)