ScottPletcher

Forum Replies Created

Viewing 15 posts - 3,271 through 3,285 (of 7,615 total)

RE: Joining on the numeric part of string...

The way I read it, you could have letters on both the front and the back of the string. This CASE code handles that situation as well:
[code...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

July 12, 2018 at 10:25 am

#1997344
RE: Can I Proper case a UK address string with postcode with a function

A tightly-written standard scalar function is another option. I'll give you mine. But you'll have to add the UK post code part to it, since this code was not written...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

July 12, 2018 at 10:11 am

#1997339
RE: Cannot reorganize an index because its disabled, but its not disabled.

Did you verify that all the constraints are "trusted"? If not trusted, perhaps that could cause an issue.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

July 11, 2018 at 11:13 am

#1997166
RE: Pivoting a 2 columns table to a 6 columns table with 1/3 the rows

A cross tab will do it nicely.

SELECT
MAX(CASE WHEN rowOrder = 1 THEN xImage END) AS xImage1,
MAX(CASE WHEN rowOrder...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

July 10, 2018 at 2:55 pm

#1997074

RE: Prevent update trigger

If the app/person/whatever doing the UPDATE is not a sysadmin, (or perhaps a dbo, not sure on that one), maybe you can just DENY UPDATE on that column:

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

July 10, 2018 at 11:43 am

#1997036

RE: Dynamic SQL using sp_executesql with DbName parameter

Another option is to create a proc in master named sp_<whatever> (the name must start with sp_), mark it as a system proc, then you can execute from any db...

July 10, 2018 at 11:33 am

#1997034

RE: Clustered index scan for few items and index seek for other records

EasyBoy - Tuesday, July 10, 2018 10:52 AM

ScottPletcher - Tuesday, July 10, 2018 10:44 AM

July 10, 2018 at 11:04 am

#1997032

RE: how do i find a specific status, and only that status

Lynn Pettis - Tuesday, July 10, 2018 9:45 AM

drew.allen - Tuesday, July 10, 2018 9:34 AM

July 10, 2018 at 10:53 am

#1997030

RE: I need to extract the 5 char length substring between dashes

SELECT *,
CASE WHEN start_of_value = 1 THEN ''
ELSE SUBSTRING(CI_NUM, start_of_value, 5) END AS [5charsyouwant]
FROM ( VALUES('asdfasf-ABCDE-asdfasdfsfasf'),('ss-TREWQ-asdfasfsf'),
('asdfa-12-ABCDE-kindatricky'),('nothingtoseehere'),
...

July 10, 2018 at 10:49 am

#1997027

RE: Clustered index scan for few items and index seek for other records

So I guess it's doing a non-clustered index seek? When you go that route, you have to be careful of the "tipping point", where SQL falls back to scanning the...

July 10, 2018 at 10:44 am

#1997024

RE: Performance Enquiry

100% agree with Phil. Get everything in one read and save/cache it for your app however you need to. Db read round trips (from your app to the db server...

July 10, 2018 at 8:14 am

#1996963