Tag: T-SQL

  • SQL Server 2025 RegEx and AI

    One of the language changes in SQL Server 2025 that I’ve seen a lot of people mention is the addition of RegEx functions to T-SQL. I decided to take a few minutes and try to examine how this feature works, and how I might use it. And more importantly, can AI help?

    This is part of a series of experiments with AI systems.

    Data with a Bit of a Pattern

    One of the common things people use Regex for is validating email addresses.

    I created a basic table in a database that looks like this:

    CREATE TABLE customer
    (
         customerid INT NOT NULL
             CONSTRAINT CustomerPK PRIMARY KEY,
         customeremail VARCHAR(200),
         validated TINYINT
    )
    GO

    I then asked PromptAI to get me some test data like this:

    2025-11_line0142

    I wasn’t connected to a database, but I still got code generated with insert statements. The AI also noted I had a “validated” column, and it populated that appropriately: 1 for good email, 0 for bad ones.

    2025-11_line0143 

    Once I run the inserts, I have data.

    2025-11_line0145

    Now, can I check that the AI did this correctly?

    REGEXP_LIKE

    In SQL Server 2025, there are a number of regular expression functions, and REGEXP_LIKE is one of these. This function is designed to return a boolean if the string_expression matches the pattern_expression, where the latter is the regular expression.

    2025-11_line0146

    Prompt sees this as a valid function for SQL Server 2025, which is great. I want to use the customeremail from the table as my string to check. For the regex, I need to create a regular expression, something I am not good at doing. I learned to do this at a very rudimentary level when writing Perl, but I’ve lost whatever little skill I used to have.

    I saw an expression on StackOverflow for validating email. Let’s check if an AI can help.

    2025-11_line0149

    In a few seconds, even on airport wi-fi (SFO as I write this), I get a response.

    2025-11_line0150

    This looks like the expression from the SO answer, though much shorter. If you read SO, you know that the format isn’t as set and stable as we’d like. In any case, let’s see what this shows.

    This is interesting. I get an error.

    2025-11_line0152

    If I look at the docs, this says it returns a true/false, which I’d assume would convert to a 1/0, but let’s try an explicit case. When I do this, it works.

    2025-11_line0153

    I can also do this in the WHERE clause, where the true/false thing just works. Let me move the function to the WHERE clause. If I do that, I have this code:

    SELECT customerid,
            customeremail,
            validated
    FROM dbo.customer
    WHERE REGEXP_LIKE(customeremail, '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')

    When I run this, I see what I expect:

    2025-11_0136

    Not a bad enhancement to the T-SQL language.

    Summary

    There’s a lot written on RegEx, so this post isn’t intended to delve deeply into what RegEx expressions you choose. Rather, I wanted to show the basics of this REGEXP_LIKE() function, and showcase one of the weird things I found when including this in the column list. I also haven’t looked at performance yet, though that certainly is something to be concerned about with larger datasets

    There are other changes in SQL Server 2025, and I’ll try to examine some in the coming weeks.

  • T-SQL Tuesday #191 Round Up

    I hosted this month, late as it turns out, but we still had a few entries. Here’s a look at the blogs people published.

    First as always, Rob Farley writes about the complexity of string parsing, using TVFs. He also shows some of the performance implications of complex parsing. Thanks to Rob for reminding me to get the invite out.

    Louis Davidson wrote about parsing HTML, which is definitely a challenge in T-SQL given the nature of the language and how embedded different tags can be. I’ve done this a little, and I prefer using Python to do this Winking smile

    Courtney Woolum writes about working with the IMDB database and needing to parse comma separated data with STRING_SPLIT and APPLY.

    Lucas Kartawidjaja brings us a look at a data outside SQL Server using the Log Parser, a free CLI tool from Microsoft that works well with data outside of SQL Server.

    Andy Brownsword tries out the new RegEx functions in SQL Server 2025.

    Hugo Kornelis hates intelligent keys because they store different types of data in the same column. He also shows some PARSENAME and CHARINDEX tricks.

  • T-SQL Tuesday #191: Invitation–Your Favorite String Parsing

    It’s that time of the month, and I’m late. My apologies. I had a mix-up with a host and was on vacation all last week, so no invite.

    I’m extending T-SQL Tuesday #191 a week to have people write on Oct 21, 2025. My apologies. The invite is below, something that’s been on my mind with all the AI work I’ve been doing, but I thought I’d ask about something more SQL related.

    I’m looking for hosts for 2026. Blogging is a great way to grow your brand and help you impress potential future employers.

    Your Favorite String Parsing Routines

    One of the things that I’ve had to do a log in my career is parse strings in different ways. It seems our customers constantly find new ways to stick data into a string that we would prefer to have normalized in some way for searching, indexing, etc.

    As a few examples, we might get someone who creates a PO that looks like 20260720321433, where there’s a year/month to start and then a number. We might need to parse either the number or the date from the field to determine when or which count of a PO someone has made. Other examples might be someone putting “Sam Jones” in the last name (surname) column because there isn’t a middle name space. We might need to get just the last name out of the field.

    This year (2025) I’ve been using AI often to try and get data from various structures, often PDFs or more complex formats, but I have managed to ask AI to change a bunch of data in a field across different rows rather than try and work out some RegEx search and replace. That got me thinking.

    What are your favorite string parsing routines that have helped you handle complex situations? That’s your invite for Oct 2025. Write on a way that you’ve handled parsing (and maybe replacing values) in complex strings.

    I see questions on things all the time at SQL Server Central. A few examples:

    • [Doe, Jane E] [Doe Smith, Jane E] (get the without middle/initials/etc)
    • [Hello5-E-100] (get the final number)
    • 192.168.1.100 – parse IPv4 (or v6) addresses
    • Parse XML for a value
    • Parse JSON for a value
    • [8/9/08 – out for upgrade; 8/16/08 returned to inventory. 8/31/2008 – deployed to field] – parse out all the dates

    Write on Oct 21, 2025, a week late, and post a comment on this post linking back to your entry. I’ll compile everything next week for the roundup. The rules:

    • Publish the post before 2025-10-21 11:59:59
    • Include the logo above
    • Link the logo to this invite
    • Make a comment on this post (or trackback/linkback)
  • The Challenge of Implicit Transactions: #SQLNewBlogger

    I saw an article recently about implicit transactions and coincidentally, I had a friend get caught by this. A quick post to show the impact of this setting.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    The Scenario

    You run this code:

    2025-09_0086

    Everything looks good. I ran an insert and I see the data in the table. I’m busy, so I click “close” on the tab and see this.

    2025-09_0087

    I’ve gotten so used to these messages, and annoyed by them in SSMS, I click “No” to get rid of it and close the window.

    The Problem

    A short while later I open a query window to do something related and check my data. I don’t see it.

    2025-09_0088

    What happened? I had implicit transactions set. This might happen if you mis-click this dialog. Ths option is close to the ANSI_NULL_DFLT_ON option.

    2025-09_0089

    You could also, or someone could in your terminal (as a poor joke) run this:

    SET IMPLICIT_TRANSACTIONS ON

    In either case, this means that instead of that insert running as expected, it really behaves like this:

    BEGIN TRANSACTION

    INSERT dbo.CityName
    (
        CityName
    )
    VALUES
    (‘Parker’)

    If I don’t explicit commit (or click “Yes”) then this isn’t committed.

    Be wary of implicit transactions. It’s a setting that goes against the way many of us work and can cause lots of unexpected problems. This is a code smell I would never want in my codebase.

    SQL New Blogger

    When I ran into this twice in a week, I decided to spend 10 minutes writing this post. It’s a chance to explain something and give a recommendation. Something every employer wants.