ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,531 through 1,545 (of 10,143 total)

RE: Inserting space after number and before letters

rash3554 (6/10/2016)
Hi there,
I have a table with home address with missing spaces between House number and streetname. How can I insert spaces?
Create table dbo.streetaddr(Address varchar(200));
INSERT INTO dbo.streetaddr...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 10, 2016 at 5:01 am

#1884341
RE: Row number with Pivot

kevin 26531 (6/10/2016)
Chris/Thomas
Thanks for the replies.
Chris - Understand your solution however the row id in it comes up in the 30K range (even if I select from 0 to...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 10, 2016 at 2:40 am

#1884306
RE: Row number with Pivot

As Thomas said. Something like this:
select *
from (
SELECT
tf.formid,
??.formdate,
??.answer,
'field-' + cast(tff.filtertype as varchar(10)) + '-' + cast(tff.id as varchar(10)) as columnname
FROM (
SELECT *,
rowid = ROW_NUMBER() OVER (ORDER...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 10, 2016 at 2:14 am

#1884301
RE: Query taking 6 hrs and counting (HELP!)- how to concatenate rows into single string

IA123 (6/8/2016)
That took 46 seconds.
I tried select * from @basedata, but it didn't work. It says 'must declare the table variable @basedata'. I'm new to sql so apologies if this...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 8, 2016 at 3:26 am

#1883815
RE: Interview Questions

Sean Lange (6/7/2016)
ChrisM@Work (6/7/2016)
Sean Lange (6/7/2016)
Luis Cazares (6/7/2016)
Try this:
SELECT sys.fn_get_folder(path, 1)
LOL that is hilarious Luis. And it seems to have fooled Lowell and Chris too. That is too funny.
Haven't you...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 7, 2016 at 9:31 am

#1883655
RE: Interview Questions

Eric M Russell (6/7/2016)
Jeff Moden (6/7/2016)
Eric M Russell (6/7/2016)
So I typically bypass the Programming 101 stuff and zoom in on what I really expect from senior level database developer.
I...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 7, 2016 at 9:13 am

#1883641
RE: Interview Questions

Sean Lange (6/7/2016)
Luis Cazares (6/7/2016)
Try this:
SELECT sys.fn_get_folder(path, 1)
LOL that is hilarious Luis. And it seems to have fooled Lowell and Chris too. That is too funny.
Haven't you got one of...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 7, 2016 at 8:53 am

#1883619
RE: Interview Questions

vs.satheesh (6/7/2016)
Thank you for ur reply
What is sys.fn_get_folder? Is it system Function ? Which version?
I don't know this function, but I know REVERSE(), CHARINDEX() and PATINDEX(), and so should you....
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 7, 2016 at 8:02 am

#1883581
RE: Replace an inverted comma with an apostrophe or typogropher's quote

SELECT NCHAR(8217); -- Alt 0146
-- Replace right closing quote with single quote
DECLARE @EmailAddress NVARCHAR(100) = 'FredO' + NCHAR(8217) + 'Shea@fred.com';
SELECT @EmailAddress;
SELECT REPLACE(@EmailAddress,NCHAR(8217),'''');
-- Replace single quote with right closing...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 7, 2016 at 4:40 am

#1883531
RE: Need to split the row values

Chris_M above has pointed out above that some of your rows appear to be truncated...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 6, 2016 at 3:22 am

#1883314
RE: divide by zero error even after predicate filters zero values inside cte with LEAD function

Plamen Ratchev explains predicate evaluation order issues in this article[/url].
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 6, 2016 at 2:35 am

#1883309
RE: Split two delimited strings in table

mikes84 (6/3/2016)
These are really great suggestions. I'm looking forward to trying them next week. Thanks so much!
Here's the original question[/url].
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 6, 2016 at 2:10 am

#1883306
RE: Need to split the row values

Look through your data to find a common rule which will identify the split location. It might be the nth tab, it might be immediately following "200SC-offer-details-May16", it might be...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 3, 2016 at 10:07 am

#1883148
RE: Need to split the row values

Each of the rows you have to split have this string in common?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 3, 2016 at 9:51 am

#1883142
RE: Need to split the row values

Thanks but that doesn't answer the question. You can determine the split point by eye because you know what to look for. What I'm trying to figure out is a...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 3, 2016 at 9:27 am

#1883129

Viewing 15 posts - 1,531 through 1,545 (of 10,143 total)