Finding a word\acronym in a field

  • Luis Cazares

    SSC Guru

    Points: 183637

    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
  • Don.

    Ten Centuries

    Points: 1293

    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%')

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    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

  • DiverKas

    SSCrazy

    Points: 2049

    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.

  • ScottPletcher

    SSC Guru

    Points: 98552

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Luis Cazares

    SSC Guru

    Points: 183637

    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
  • Luis Cazares

    SSC Guru

    Points: 183637

    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
  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    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

  • Don.

    Ten Centuries

    Points: 1293

    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 24 (of 24 total)

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