Jeff Moden

Forum Replies Created

Viewing 15 posts - 51,031 through 51,045 (of 59,102 total)

RE: Order by Numeric value of a Varchar column Need a better solution

ALZDBA (4/21/2008)
Jeff Moden (4/20/2008)
Here's why I say NOT to use ISNUMERIC as an "IsAllDigits" function...
Indeed, I forgot about the currency signs. :crying:
Thank you for providing the faulty example.
They'd better remove...
--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 21, 2008 at 5:36 am

#804867
RE: Finding Date & time from a string

Chris used the method I would have. Ryan's is also a viable solution.
--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 21, 2008 at 5:30 am

#804862
RE: Replace non numeric characters in string

Sorry guys, I lost track of this one... what ended up winning the race on the 4 million row test? RegEx or one of the functions?
--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 20, 2008 at 10:33 pm

#804755
RE: Deserialize data in xml column to a table

Kaushik Kumar (4/20/2008)
Nevermind, figured out the comma part.
Two way street here, Kaushik... how about telling us how you did 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 20, 2008 at 9:49 pm

#804752
RE: query to find space usage of each column in a table

Then, try this on a couple of the VARCHAR(255) columns...
SELECT *
FROM T12
WHERE DATALENGTH(columnname)-LEN(columnname) <> 0
If you get any returns, it may be because data...
--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 20, 2008 at 9:47 pm

#804751
RE: query to find space usage of each column in a table

Try this first... check Books Online for how to read the output...
DBCC SHOWCONTIG (T12) WITH TABLERESULTS, ALL_INDEXES
--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 20, 2008 at 9:40 pm

#804750
RE: Altering Column Values

Are the columns defined as CHAR() instead of VARCHAR()? Depending on system settings, that could be the problem.
--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 20, 2008 at 9:27 pm

#804748
RE: Problem with calculated column formula

This is how you could do it in a view... but a view is not needed. This also works directly in code. It works because there is only...
--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 20, 2008 at 9:25 pm

#804747
RE: Order by Numeric value of a Varchar column Need a better solution

mail2dinesh.sharma (4/20/2008)
333233343MVF is such example. Until now I have seen only alphabets and numbers but not any special characters. Beside the value I receive is from an external source so...
--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 20, 2008 at 9:16 pm

#804744
RE: Order by Numeric value of a Varchar column Need a better solution

Here's why I say NOT to use ISNUMERIC as an "IsAllDigits" function...
[font="Courier New"] SELECT ISNUMERIC('143563d21'),
ISNUMERIC('143563e35')
----------- -----------
1 ...
--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 20, 2008 at 9:15 pm

#804743
RE: Create and Populate Time Dimension

colin Robinson (4/20/2008)
i would also probably drop the table rather than truncate in this instance. You would then get the same Identity value for each date each time you ran...
--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 20, 2008 at 4:55 pm

#804726
RE: Altering Column Values

It would appear that the value for the "val" column is incorrect thoughout the table. It should not have trailing spaces. I'd recommend finding the root cause for...
--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 20, 2008 at 4:51 pm

#804725
RE: Problem with calculated column formula

SELECTs aren't allowed in calculated columns. Nothing you can do about it. Use a view, instead. Be careful when you write the view... improperly written views can...
--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 20, 2008 at 4:47 pm

#804724
RE: Substitute NULL value on converted date field?

john.arnott (4/18/2008)
It's been suggested that "COALESCE" is preferable to ISNULL.
It allows for a list of arguments, the first of which evaluates to not null being returned.
Coalesce is ANSI standard; ...
--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 20, 2008 at 4:45 pm

#804723
RE: Order by Numeric value of a Varchar column Need a better solution

Dinesh,
How about providing some additional samples where the TradeReference column contains the Alpha characters you're talking about?
--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 20, 2008 at 4:42 pm

#804722

Viewing 15 posts - 51,031 through 51,045 (of 59,102 total)