Case sensitive REPLACE? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Case sensitive REPLACE?

Author

Topic

shawntracy01
Starting Member

4 Posts

Posted - 2007-11-12 : 11:52:31

I am trying to highlight search terms on a web page, which are coming out of a SQL DB. The DB is case-insensitive, which is ideal for us. I'm trying to do it something like this:

SELECT REPLACE(field_name, @searchString, '' + @searchString + '')...

This works, however, I lose the case-sensitivity in the original field. For example, if the search term was 'the', it will find 'The', 'THE', 'the', etc, which is what we want, however it will replace them all with lowercase 'the', which we don't want. Any ideas on how to handle this, or maybe I should be taking a completely different approach to the problem? Any help would be great.

TIA
Shawn

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-12 : 12:02:00

look into COLLATE, something like this may be what you want...

WHERE field_name COLLATE SQL_Latin1_General_Cp1_CS_AS like '% ' + @searchString + ' %'
OR field_name COLLATE SQL_Latin1_General_Cp1_CS_AS like @searchString + ' %'
OR field_name COLLATE SQL_Latin1_General_Cp1_CS_AS like '% ' + @searchString

shawntracy01
Starting Member

4 Posts

Posted - 2007-11-12 : 12:16:51

Thanks for the quick response...

Maybe I'm not getting the point of your idea - I understand that using COLLATE that way will help me to find all the instances, but REPLACE is actually doing that fine. The problem I'm having is replacing the search term with the case that it is originally.

If you could give me an example of how I could use COLLATE to this end, that would be great.

Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 12:31:15

You need to specify the replace string case sensitive as well using the COLLATE.

'' + @searchString COLLATE SQL_Latin1_General_CP1_CS_AS + '')..

Poor planning on your part does not constitute an emergency on my part.

shawntracy01
Starting Member

4 Posts

Posted - 2007-11-12 : 12:59:08

Sorry if I'm being dense, but I'm not getting the results I want.

For the example of "the" as the searchString, if I do this:

SELECT REPLACE(field_name, @searchString, '' + @searchString + '') FROM table

I get this:
the = the
The = the
THE = the

If I do this, as you suggest (I think?)
SELECT REPLACE(field_name, @searchString COLLATE SQL_Latin1_General_CP1_CS_AS , '' + @searchString + '') FROM table

I get this:
the = the
The = The
THE = THE

I'm looking for this result set:
the = the
The = The
THE = THE

I'm not sure what I'm missing here.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 13:20:47

if I understanding your meaning, you want to find the string (not case sensitive on the find), but you want to replace it with the original case found. That means you specify case sensitive on the replace part, not the search part. So you want to put the Field_Name in the replace. not the search string because by using the search string it will always populate with however you type the string


SELECT REPLACE(UPPER(field_name), UPPER(@searchString) , '<span class="highlight">' + field_name + '</span>') 
FROM table

based on:



Declare @searchstring varchar
Select @searchstring = 'the'

Create Table #t (samp varchar(50) not null)

Insert Into #t
Select 'the' Union ALL
Select 'THE' Union ALL
Select 'thE' 

SELECT REPLACE(Upper(samp), Upper(@searchstring) , '<span class="highlight">' + samp + '</span>') 
FROM #t

Drop Table #t

which returns this:


<span class="highlight">the</span>
<span class="highlight">THE</span>
<span class="highlight">thE</span>

Poor planning on your part does not constitute an emergency on my part.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-12 : 14:56:29

I think you'd have to write a function to do that.

Dataguru,

He wants to retain the original casing of the string but wrap it with some text (in this case a span). I changed some of your data to show what is not working (notice all the THE's are replaced to what that search string is not what the original "the" casing is:

Declare @searchstring varchar(3)
SET  @searchstring = 'the'

Create Table #t (samp varchar(50) not null)

Insert Into #t
Select 'This is the greatest of all time!' Union ALL
Select 'This is THE greatest of all time!' Union ALL
Select 'This is thE greatest of all time!' 

SELECT REPLACE(samp, @searchstring , '<span class="highlight">' + @searchstring + '</span>') 
FROM #t

Drop Table #t

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-12 : 15:11:52

looking this over again, the search is case insensitive, but the results need to retain original case. this of couse does not take partial match ("the" finds "there") into account, but is a starting point...

Declare @searchstring varchar(3)
SET @searchstring = 'the'

Create Table #t (samp varchar(50) not null)

Insert Into #t
Select 'This is the greatest of all time!' Union ALL
Select 'This is THE greatest of all time!' Union ALL
Select 'This is thE greatest of all time!'

SELECT
LEFT(samp, CHARINDEX(@searchstring, samp) - 1) +
'' +
RIGHT(LEFT(samp, CHARINDEX(@searchstring, samp) + LEN(@searchstring) - 1), LEN(@searchstring)) +
''
FROM #t

Drop Table #t

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 15:24:46

Edit: we both had the same thought. I knew of the wrapper when I posted that one bit, but didn't take into account the full string of the fields value.


Declare @searchstring varchar(3)
SET  @searchstring = 'the'

Create Table #t (samp varchar(50) not null)

Insert Into #t
Select 'This is the greatest of all time!' Union ALL
Select 'This is THE greatest of all time!' Union ALL
Select 'This is thE greatest of all time!' 

Select patindex('%' + @searchstring + '%',samp)
FROM #t

SELECT left(samp, patindex('%' + @searchstring + '%',samp)-1) + '<span class="highlight">' + 
substring(samp,patindex('%' + @searchstring + '%',samp),len(@searchstring)) + '</span>'+
right(samp,len(samp)+1 - patindex('%' + @searchstring + '%',samp)-len(@searchstring))

FROM #t

Drop Table #t

The OP wants the results of a search of "the" to return matches, and as you say wrapped with the html text around the matched word, but wants the matched word to retain it's case syntax.

results of above:
This is the greatest of all time!
This is THE greatest of all time!
This is thE greatest of all time!

which match the OP's request and seems to fit, although it will not be useable of the search string appears more than once in the full string, so it still needs some work to produce the requested results.

EDIT: Anonymous and I both posted the same re-thinking at the same time. Jinx!

Poor planning on your part does not constitute an emergency on my part.

shawntracy01
Starting Member

4 Posts

Posted - 2007-11-12 : 16:34:09

Thanks for all the responses, I really appreciate them.

Unfortunately I'm not quite there yet... This is going to need to work on potentially the full text of articles, so having it only work for the first occurence isn't enough, so I don't know if PATINDEX is going to work. Also it seems to blow up if the search string isn't contained in the field - this has to work on field values that potentially don't include the search string.

Thanks again...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 16:59:37

Well the original works to replace all occurrences of the word in the field value, but finding a way to replace all occurences AND keep the original occurences text would require a UDF of some kind.
this was the original working (but not retaining original case)

replace(field,@searchstring,'' + @searchstring + '')

Poor planning on your part does not constitute an emergency on my part.

adamvaul
Starting Member

1 Post

Posted - 2011-08-25 : 14:50:33

I created a function in sql that follows this logic

DECLARE @MatchText VARCHAR(250)= 'se'

DECLARE @Text VARCHAR(MAX) = 'This security is Selling'

SELECT REPLACE(@Text, @MatchText, '' + @MatchText + '')

DECLARE @ParseTable TABLE
(
CommentText VARCHAR(MAX) ,
MatchText VARCHAR(250)
)

DECLARE @Index INT = 0

SET @Index = PATINDEX('%' + @MatchText + '%', @Text)

WHILE ( @Index > 0 )
BEGIN

INSERT INTO @ParseTable
( CommentText ,
MatchText
)
VALUES ( SUBSTRING(@Text, 0, @Index) ,
SUBSTRING(@Text, @Index, LEN(@MatchText))
)

SET @Text = SUBSTRING(@Text, @Index + LEN(@MatchText), 8000)

SET @Index = PATINDEX('%' + @MatchText + '%', @Text)
END

SELECT *
FROM @ParseTable

UPDATE @ParseTable SET MatchText = '' + MatchText + ''

SELECT *
FROM @ParseTable

DECLARE @MarkedComment VARCHAR(MAX) = ''

SELECT @MarkedComment = @MarkedComment + CommentText + MatchText FROM @ParseTable

SET @MarkedComment = @MarkedComment + @Text

SELECT @MarkedComment

Adam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources