Finding a word\acronym in a field

Question

Finding a word\acronym in a field

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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 More actions · Answer 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%')

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

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 More actions · Answer 4

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: 100950 More actions · Answer 5

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) A socialist is someone who will give you the shirt off *someone else's* back.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

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: 183698 More actions · Answer 7

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: 182276 More actions · Answer 8

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 More actions · Answer 9

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[ ,]%'