Search and replace using a wildcard 1

[Minimorgie]

Hi,
I've trawled previous threads to see if I can find an answer (that's clear to me!) to my problem but no joy. Anyway, I'm using SQL scripts to extract data from a source database into a reporting database. Some of the data contains text which I need to remove e.g.
### Bronte Rent ### this is always at the start of the data and is always contained within the # signs but what is between the # signs varies in content and lenght.
I've tried using REPLACE but it's not possible to use a wildcard.
Can anyone suggest anything that a basic level user can understand? The tablename is pamwin_supplemental and one of the fieldnames is notes.
Many thanks

 

[markros]

You may want to use STUFF function instead, e.g.

select Notes, F1.Pos, F2.Pos2, case when F2.Pos2 > F1.Pos then
stuff(Notes, F1.Pos, F2.Pos - F1.Pos, '') as CleanedNotes
from PamWin_Supplemental
CROSS APPLY (select CharIndex('#',Notes) as Pos) F1
CROSS APPLY (select CharIndex('#',Notes, F1.Pos) as Pos2) F2

--------------------------
The above is from the top of my head and not tested - you may need to tweak it a bit. Also it's SQL Server 2005+ syntax - would not work in SQL Server 2000.

PluralSight Learning Library

 

[gmmastros]

This can get a little complicated, but I will try to explain my thought process here.

First, you should become familiar with the various string handling functions available in TSQL. (Like CharIndex, PatIndex, SubString, Left, Right). PatIndex allows for a wildcard search, but in this case, you know what you are searching for, you just don't know where in the string it is found.

I would suggest a combination of substring and CharIndex. Charindex returns the position within a string where another string is found. For example...

Declare @Data VarChar(1000)

Set @Data = '### Bronte Rent ###This is more text.'

Select CharIndex('###', @Data)

If you copy/paste the code above in to a query window and run it, you will see that the charindex function returns the value 1 because 3 consecutive # symbols is found starting at the first character. I admit that this isn't very helpful for you, but wait.... there's more. CharIndex has an optional 3rd parameter that lets you specify the starting position of the search. If you set that 2rd parameter to 4, you will get the starting position of the next set of # symbols.

Declare @Data VarChar(1000)

Set @Data = '### Bronte Rent ###This is more text.'

Select CharIndex('###', @Data[!], 4[/!])

The code above returns 17 because that is where the 2nd set of #'s begin. To get the string to the right of the 2nd set of #'s, we can use substring, like this...

Declare @Data VarChar(1000)

Set @Data = '### Bronte Rent ###This is more text.'

Select [!]SubString(@Data,[/!] CharIndex('###', @Data, 4)[!], 8000)[/!]

Notice that this includes the 3 # symbols, so we can just add 3 to the starting position of the substring, like this:

Declare @Data VarChar(1000)

Set @Data = '### Bronte Rent ###This is more text.'

Select SubString(@Data, CharIndex('###', @Data, 4) [!]+ 3[/!], 8000)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Minimorgie]

Hi gmmatros,
I have copied your scripts and got the hang of what you mean but when I try it on my live data using the following :

Declare @site_info VarChar(1000)

Set @site_info = (select distinct cast(note as varchar(500)) from schemenotes)

Select SubString(@site_info, CharIndex('###', @site_info, 4) + 3, 8000)

I get the error message subquery returned more than one value .......

What am I doing wrong?

 

[markros]

This code
Set @site_info = (select distinct cast(note as varchar(500)) from schemenotes)

is not going to work if you have more than 1 distinct notes in your table.

You need to use

Select @Site_into = cast(Note as varchar(500)) from SchmeNotes

in order to get the very first Note from the table.

Is it the intent?

BTW, what is your SQL Server version? If SQL Server 2005+ then I suggest to give my idea a try.

See a similar idea applied here

http://social.msdn.microsoft.com/Fo...l/thread/6d126edb-b89f-4237-bd15-e045f9dcc049

PluralSight Learning Library

 

[Minimorgie]

The use of STUFF will not work for me because our SQL version is 2000. What I am trying to achieve is a list of the notes (without the ### etc where it is included in the notes - not always) along with the unique identifier - in this case schemeid. The notes come from the tableschemenotes and are written to the pamwin_supplemental table. You previous suggestion did not work I'm afraid.

Thanks for your help.

 

[gmmastros]

Stuff does work with SQL2000. The syntax can be a little tricky, but I've used it before.

Anyway.... My earlier post used a variable so that it was easier to explain HOW it works. You can easily modify the code to use the value from a table.... something like this:

Select SubString(YourColumnName, CharIndex('###', YourColumnName, 4) + 3, 8000) As NotesWithoutHeaderStuff
From   YourTableNameHere

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Minimorgie]

Hi,

Thanks for your help with this. In the end I decided to write everthing to a temp table using the following SQL :

insert into rag_temp_notes
(schemenoteid, schemeid, notedate, notetypeid, note)
select schemenoteid, schemeid, notedate, notetypeid, substring(note,charindex('###', note,4) +3,800)
from schemenotes where note like '%##%' ;

This wrote all notes with the ###... chars in but with these chars removed) and I then wrote in all the other notes where ###.. was not present, this temp table was then the source of the 'sorted out data' for use in my reporting. It might be long winded and may not be the best way of doing it but it works and the database is not so massive that there's an impact on performance.

Many thanks again.