Finding a word\acronym in a field

  • That's because the first script is expecting a character after and a character before the LC, that's why I added trailing and leading spaces to the column. Try adding a value like ABC,LC,XYC to test this point.

    Edit: damned autocorrect that included fiesta instead of first

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks for the reply Luis.

    This script seems to do what I want with the demo data.

    Select top 100 *

    From CONTSUPP

    Where RECTYPE = 'C'

    AND ( CONTSUPREF = 'LC'

    OR CONTSUPREF like '%[ ,]LC[ ,]%'

    OR CONTSUPREF like '%LC[ ,]%'

    OR CONTSUPREF like '%[ ,]LC%')

  • A CLR function that uses regular expressions, finding a "word" in a string that equals "LC" is a simple reg ex task, could also be a good option to explore.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I would also add, that depending on a lot of variables like size of the field, number of rows, density etc, Full Text Search (FTS) might be considered as well.

  • WHERE

    ',' + column + ',' LIKE '%[^a-z0-9]LC[^a-z0-9]%'

    Add other char(s) if/as needed; for example, if you prefer that underscore, such as in "LC_AXIS", not be a "word" break, then do this:

    WHERE

    ',' + column + ',' LIKE '%[^a-z0-9_]LC[^a-z0-9_]%'

    Note: if you want to use dash (-) itself in the like string, you should make it the last character, otherwise SQL will take it as a range indicator, and not a separate character.

    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".

  • opc.three (6/12/2013)


    A CLR function that uses regular expressions, finding a "word" in a string that equals "LC" is a simple reg ex task, could also be a good option to explore.

    Could you give an example?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Don. (6/12/2013)


    Thanks for the reply Luis.

    This script seems to do what I want with the demo data.

    Select top 100 *

    From CONTSUPP

    Where RECTYPE = 'C'

    AND ( CONTSUPREF = 'LC'

    OR CONTSUPREF like '%[ ,]LC[ ,]%'

    OR CONTSUPREF like '%LC[ ,]%'

    OR CONTSUPREF like '%[ ,]LC%')

    You're adding cost to the filter, why wouldn't you leave it as I suggested? or used Scott solution which is doing the same thing just the opposite way.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Reprinting from Simple Talk article CLR Assembly RegEx Functions for SQL Server by Example[/url] by Phil Factor:

    Imports System

    Imports System.Data.Sql

    Imports Microsoft.SqlServer.Server

    Imports System.Data.SqlTypes

    Imports System.Runtime.InteropServices

    Imports System.Text.RegularExpressions

    Imports System.Collections 'the IEnumerable interface is here

    Namespace SimpleTalk.Phil.Factor

    Public Class RegularExpressionFunctions

    'RegExIsMatch function

    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _

    Public Shared Function RegExIsMatch( _

    ByVal pattern As SqlString, _

    ByVal input As SqlString, _

    ByVal Options As SqlInt32) As SqlBoolean

    If (input.IsNull OrElse pattern.IsNull) Then

    Return SqlBoolean.False

    End If

    Dim RegExOption As New System.Text.RegularExpressions.RegExOptions

    RegExOption = Options

    Return RegEx.IsMatch(input.Value, pattern.Value, RegExOption)

    End Function

    End Class '

    End Namespace

    The call for this problem case might be:

    DECLARE @pattern NVARCHAR(4000) = N'\bLC\b';

    WITH cte(string)

    AS (

    SELECT 'LC, AB'

    UNION ALL

    SELECT 'LC,AMB'

    UNION ALL

    SELECT 'LCAND, CMB'

    UNION ALL

    SELECT 'ABC, LC'

    UNION ALL

    SELECT 'LC'

    UNION ALL

    SELECT 'Welcome'

    )

    SELECT *,

    dbo.RegExIsMatch(@pattern, string, 1) AS IsMatched

    FROM cte;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Luis Cazares (6/12/2013)


    Don. (6/12/2013)


    Thanks for the reply Luis.

    This script seems to do what I want with the demo data.

    Select top 100 *

    From CONTSUPP

    Where RECTYPE = 'C'

    AND ( CONTSUPREF = 'LC'

    OR CONTSUPREF like '%[ ,]LC[ ,]%'

    OR CONTSUPREF like '%LC[ ,]%'

    OR CONTSUPREF like '%[ ,]LC%')

    You're adding cost to the filter, why wouldn't you leave it as I suggested? or used Scott solution which is doing the same thing just the opposite way.

    I rather suspect its because Im a bit of an idiot. 😉

    I didn't realize \ understand the purpose of the spaces around your String.

    I've amended the script as you suggested.

    Select top 100 *

    From CONTSUPP

    Where RECTYPE = 'C'

    AND ' '+CONTSUPREF+' ' like '%[ ,]LC[ ,]%'

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply