The Heap™ – Consultancy ©®

7:04 AM

Morning

7:17 AM

Morning+0300

7:44 AM

Hi. I'm trying to understand why does this code work.

I'm trying to remove trailing zeros

select substring('0050678', patindex('%[^0]%','0050678'), 100)

REsult : 50678

The regex : match any part of the string where it does not in the set of (0)

@RoyiNamir Define not working, because on my system (SQL Server) it returns exactly that

it is working. I jsut don't understand why

I don't understand how come this regex yields the desired result.

what about this zero ?

The pattern in patindex means "any character that is not a 0".

So the function returns the position of the first non-0 character.

oh it returns the FIRST position

my bad sorry

substring uses the result as the starting position of the substring to extract from the source string.

7:50 AM

Thank you.

Oh but it doesn't work on this :

select substring('0', patindex('%[^0]%','0'), 100)

Result : 0

(basically im trying to remove leading zeroz from a phone number , becuase our SMS provider requires it. Sure I can do it in C# , but i'd like sql to do that)

patindex doesn't find a non-0 character in 0. When there's no match, the function returns 0. And if substring takes 0 as the starting position, it peculiarly treats it as position 0 of the source string.

Is there anyway I can fix it ? so it return '' ?

(In that case)

Just append an arbitrary character to the source string in patindex.

select substring(@SourceString, patindex('%[^0]%',@SourceString + '.'), 100)

Ok. now I don't understand the trick :(

patindex of first non zero in 050678. ? this yields index 2.

(1 based)

oh I got it.

and then you take the index and cut from the original SourceString

very nice :)

Yep, well done :)

In case of an all-0 string (or an empty string), the result will be an index beyond the last character of the source, but that's fine, substring handles that well.

8:03 AM

yeah i was expecting out of boundry exception

The forgiveness of SQL....

Darth_Wardy

8:23 AM

forgiveness ... jeez that's a hack surely ?

I really don't like the way SQL handles regex :(

Hey guys, I just need to confirm something quickly - say I have a table with 3 columns - A, B and C, and create a view on that table, which has the same columns - A, B and C, plus one column D which is calculated based on columns B and C. Is there any method to change the value in view.D for a given record without altering the original values in columns of the physical table?

@ROAL What do you mean by "change the value in view.D for a given record"? A view isn't really a table, it's just a query. Whatever values it returns are not really stored somewhere so that you could change them.

And while you can write to view.A, view.B or view.C because there are actual table columns behind those, you can't write to view.D, because that's a calculated column.

@ROAL You could rig up something to do this using ISNULL, eg ISNULL( colE, colB * colC )

where colE is normally NULL, and updating it means that value will return first

but this seems like a slightly strange requirement to me.

Maybe a question with some more detail?

8:43 AM

Yeah, that's what I thought, @AndriyM , @wBob. Thanks for the tip. This is mostly a question of how to overcome the limitations of a proprietary software sold by a company that mostly refuses to do anything that they don't consider "standard".

@ROAL Do you have control over the view definition?

@AndriyM the software uses our own SQL server, so we have full control over the database, however, I'd like to avoid tampering with the actual tables. The company went on a little rage fest even over adding a new column to the view (while the software handles this nicely, as the columns that get selected are actually chosen by each user).

9:01 AM

@ROAL Can you add a(n auxiliary) table?

(and redefine the view to use that table for the D column)

9:23 AM

@AndriyM that's what I'm thinking now. That would also require a trigger, but that should be fine.

@ROAL I don't see how you would need a trigger. Unless you mean creating a row in the new table for each row of the "main" table (which I so far think is unnecessary, but maybe it has to do with something you haven't told us).

But in any event, if you already know how to solve your problem, that's what really matters.

11:36 AM

@RoyiNamir what's wrong with select cast('0050678' as int) ?

why the hackery?

@Marian That's supposed to be a phone number, so it might not fit into an int.

@AndriyM makes sense then

ConcernedOfTunbridgeWells

12:02 PM

Is it considered OK to have your homepage in your profile link to a site with this title: Here I will Give Cracked Softwares

Ninja, Vancouver, BC, Canada

1 2

Not that there is much cracked software there (yet)

@Darth_Wardy There's quite a good reason for SQL's limited regex capabilities, and that's because you can't convert a regex into a sarg-able search. Ordinary wildcards can be sarg-able in some circumstances (where you have a prefix). Regex searches will necessitate a table scan or other O(n) operation.

3

A: Regular Expressions in SQL Server servers?

The answer is no, not in the general case, although it might depend on what you mean by efficient. For these purposes, I'll use the following definition: 'Makes effective use of indexes and joins in a sensible order' which is probably as good as any. In this case, 'Efficient' queries are 's-arg...

4

A: Database or structure suitable for matching strings to regex patterns

The short answer is 'No.' There is no index structure currently available on any DBMS platform that will index partial matches of a regex like this. The long answer is that a leading constant on a wildcard match (e.g. 'foo_') can be used as a prefix for index matches. Many DBMS platforms wil...

Having said that, there's no intrinsic reason why it couldn't support regex matching, although the inefficiencies above will always come into play.

Q: Intermittent error while adding comment and voting questions on Stackexchange

12:28 PM

@TomV Still not a good impression for the site, I think. "Oh, look what kind of people these free help sites attract."

But I'm really not sure what I'd do about it.

Master Database

0

This is basicaly something I am facing for couple of months. Sometimes when I try to add comment to a question or answer or try to vote question I get error An Error Occurred-Please Retry your Request Below is screenshot of what I am referring to. I was trying to vote up on question and go...

discussion

@AndriyM Me neither, that's why I asked around here

maybe it should go on meta but I didn't want to publicly shame the guy on his first day around either

ypercubeᵀᴹ

Morning from PGdayUK 2016

@TomV On his first month, you mean.

@AndriyM Technically yes but he didn't do anything before today

oh, on SO, sorry yes you are right

12:41 PM

He also posted a question yesterday. And for some reason I think I saw another question of his a week or two ago.

I might be confusing him with someone else, though.

Paul White

2:44 PM

@TomV It's probably not a good look, but on balance I don't feel minded to edit the profile to remove it. If you wanted to gauge how the community feels about it, you could ask a general question about the broad issue - links in profiles - and see what people say.

3:03 PM

@PaulWhite I'll leave it at that then

I just noticed it and found it a bit weird, nothing I feel very strongly about

swasheck

4:22 PM

> John1June 16, 2012 12:46 am
This blog of yours never seizes to amaze me.

James Lupolt

Hi

billinkc

@swasheck Trying to become an authority?

Lamak

an authority about SQL

swasheck

@billinkc super funny

@mmarie dork?

@MikeFal ... what can powershell do to me?

4:55 PM

@swasheck nothing, you're immutable

swasheck

@Marian slip of the tongue in the intro and i saw mikes face go crazy

ha, did he do a crazy face?

didn't watch it yet completely, just listened for the first videos and booked for the weekend

6:04 PM

Too harsh/cynical to a native English speaker?

Hi, welcome to the site. Please don't overdo the bold and the font size, it makes this question unreadable. OK maybe not unreadable, but it doesn't incite effort from someone trying to parse it. Proper formatting does. — Tom V 51 secs ago

6:15 PM

Apparently it was not so much the formatting as the lack thereof. They just have a script with many #'s

@AndriyM Oh yes I see now

6:31 PM

Hola

My ignorance of the statistics has come back to bite me since I picked up an Azure SQL DW project, and DW doesn't auto update statistics. Anyone recommend a good beginner's guide to statistics? Web or book or other is fine.

@swasheck?

billinkc

Leave it statistics free and let MS reap the windfall

@billinkc unhelpful

billinkc

+1, would comment again

@billinkc Oh they'll fix that if everybody starts trashing their shared storage :)

@mmarie Otherwise I have nothing useful to add except, do you have query store where you could query the plans for warnings about missing statistics?

Unfamiliar with Azure DW

I don't believe Azure DW has query store

From what I know now, updating statistics is very important, and I get Clustered Columnstore tables by default.

6:42 PM

Maybe SQLSentry's tools have something but Aaron is never around anymore

They have some Azure DW monitoring IIRC

After that, I give up

Thanks

Maybe a question on main might attract useful input

I'd upvote that, I consider it interesting now that you raised the point, before it was an unknown issue :)

@TomV I don't have a real question yet. Just need to understand and have a ton to learn, so was looking for pointers to good resources.

@mmarie You read this?

This I suppose you already read?

Yes, to the second, but no to the first. Thanks.

6:49 PM

@mmarie I'm interested because we will soon be moving some installs to azure (not DW, but including columnstore stuff) and it kind of scares me

@TomV Azure SQL Database doesn't work the same

You have query store and auto update stats there

I actually just did an Azure SQL database project. But DW is a different beast with the MPP stuff.

Lamak

7:29 PM

@mmarie it is @billinkc

A: Multiple PVSCSI with SQL Server

7

I'll answer in two parts: first "why the traditional answer about separating sequential and random often doesn't apply." Then I'll discuss the potential benefits of separating files at the Windows physicaldisk, and to adding additional vHBAs and distributing the physicaldisks among them. Expect...

+50 for canonical answer and future dupe target

11:23 PM

@mmarie I did a project with this interesting and powerful product last Christmas. The JRJ stuff is golden on this, and honestly there isn't a great deal more documentation other than that and the Azure docs online.

There is some obvious stuff you need to know like you can't use MERGE, other limited T-SQL surface area, learn how to use CTAS, Polybase is very fast for getting data in, SSIS not so much, we didn't get round to using Azure Data Factory.

There is a migration wizard which is worth running - here's some sample output which is really instructive as to what you can and can't do. The new July 2016 version of SSMS supports Azure SQL Datawarehouse now, so that's nice : )

The output from EXPLAIN your_query is interesting to look at and if you can learn the difference between round robin and hash distributed tables, you're well on your way.

James Lupolt

@TomV Yes, glad to see @sql_handle from Twitter here

@wBob Yes I think JRJ's job is basically to help people understand how to use Azure SQL DW. Would think he'd answer a question about managing stats if asked.

@JamesLupolt Pretty sure I've seen some code to automate ADW stats build somewhere.

@mmarie There is a really really awful book by TeraTom purportedly about Azure SQL Data Warehouse, but avoid like the plague. This book is practically a crime.

[shuffles awkwardly as TeraTom enters the room]. I hi TeraTom, didn't see you there...

James Lupolt

11:48 PM

Is TeraTom TomTom?