Search results for query: *

This is pretty ugly but might provide the results you're after... SELECT SUBSTRING(TextColumn, PATINDEX('%[A-Z][A-Z]-[0-9]%|%', TextColumn), CHARINDEX('|', TextColumn, PATINDEX('%[A-Z][A-Z]-[0-9]%|%', TextColumn)) - PATINDEX('%[A-Z][A-Z]-[0-9]%|%', TextColumn)) YourValue FROM YourTable...

If you're only interested in the results of that exact query without making any tweaks to it, consider assigning it to a Query Shortcut. Tools > Options > Environment > Keyboard > Query Shortcuts Copy the query to one of the shortcuts, then just press the Ctrl combination to display the results...

Glad you were able to solve the problem. This isn't related to the original post but may I make a suggestion? In my opinion it would make the code more readable. Declare a 2-char variable to replace all of your CHAR(13) + CHAR(10) references... DECLARE @CrLf CHAR(2) = CHAR(13) + CHAR(10) ...

Going back to your original post It's been a while since I've worked in VBA but I would think you would want to work with the CodeName which is the name of the worksheet that is not in parenthesis in the Project - VBAProject panel. I would rename Sheet1 to something like InvoiceSheet and then...

I'd also move the check for @MaxDateDuplicateId > @MaxDateMergeToId to the outer IF statement. May as well do the simple variable comparison first before any statements that access tables.

Once you have a row number (from either a CTE or Temp table), you can calculate the third column value with a CASE statement like... CASE WHEN RowNo = 1 THEN '0' WHEN RowNo % 4 = 1 THEN CAST((RowNo / 4) + 0 AS VARCHAR) + '.4' WHEN RowNo % 4 = 0 THEN CAST((RowNo / 4) + 0 AS VARCHAR) +...

Could you process all unprocessed quotes like this? DECLARE @UnprocessedQuotes TABLE (quote_no VARCHAR(16)) INSERT INTO @UnprocessedQuotes SELECT DISTINCT quote_no FROM @Table WHERE quote_print = 'DT' AND Sub_Total IS NULL DECLARE csr CURSOR FOR SELECT quote_print, Total_After_Discount...

Here's an attempt without using a cursor... IF (OBJECT_ID('tempdb..#TableWithRowAndGroup', 'U') IS NOT NULL) DROP TABLE #TableWithRowAndGroup SELECT ROW_NUMBER() OVER (ORDER BY Sub_Quote_Code, Sort_Order) RowNo, CAST(NULL AS INT) GroupNo, * INTO #TableWithRowAndGroup FROM @Table UPDATE...

Would it be as simple as this? ;WITH t AS ( SELECT Manufacturer, OldCityMfgKey, NewCityMfgKey, ROW_NUMBER() OVER (PARTITION BY Manufacturer, OldCityMfgKey ORDER BY NewCityMfgKey) RowNo FROM YourTable ) SELECT * FROM t WHERE RowNo = 1

I think you will need the RANK function to accommodate ties. ;WITH t AS ( SELECT ClientID, Date, Location, RANK() OVER (PARTITION BY ClientID, Date ORDER BY COUNT(*) DESC) FreqRank FROM @Table GROUP BY ClientID, Date, Location ) SELECT ClientID, Date, Location FROM t WHERE...

Since there is no inherent order to rows in SQL you'll need to assign an order before matching up rows. I'm going on the assumption that the lowest Start_Time_1 should match to the lowest Start_Time_2 and so on, but what happens if the are more rows of one type than the other? ;WITH t1 AS (...

I'm wondering if anyone can explain this behavior. The example uses 2 controls on a form, comboBox1 & textBox1. Both of their Visible properties are true in the designer. When I step through the code I can see where the Visible property of each control changes to false after they are added to...

Use OUTER APPLY instead; it's the equivalent of a LEFT JOIN.

There have been times when I’ve needed a specific format for a DateTime value but don’t have the different style values memorized. Instead of doing a search on the CONVERT function each time, I created this query shortcut and thought I’d share. Go to (SSMS 2014) ... Tools > Options… >...

Here are a couple other options... Dim NamesToSkip As String NamesToSkip = "*TOC*Cover*End*Disclaimer*Letter of presentation*LoP*Glossary*" For Each aSheet In Worksheets Dim Name As String Name = Trim(aSheet.Name) Dim FormatTab As Boolean...

I guess I'm not clear on when the unique suffix is to be generated; during bulk import or at a later date? Why not just use an identity value to make each row unique?

How about? DECLARE @Things TABLE (Name VARCHAR(50), Number INT, SomeDate DATE, Type VARCHAR(16), BiggerNumber INT) INSERT INTO @Things VALUES ('AcmeTools', 20000, '1/15/2018', 'Credit', 12345678), ('AcmeTools', 20000, '1/15/2018', 'Credit', 12345678), ('AcmeTools', 20000, '1/15/2018'...

A couple things to note; you'll never do more than 1 pass because you are updating all mytable rows with a non-null folder value and there are a couple shortcuts you could use DECLARE @foldernumber int = 1 -- [highlight #FCE94F]Declare and initialize on 1 statement[/highlight] WHILE (EXISTS...

Typically when you see ugly code like that, it means it was created by a code generator such as Query by Example.