SQL Function Reg Expression - 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

General SQL Server Forums

New to SQL Server Programming

SQL Function Reg Expression

Author

Topic

techglider
Starting Member

26 Posts

Posted - 2009-07-23 : 11:43:50

This function will in part be just the removal of a affixated string to the end of some of the column data that I have.. I am removing (where found) the following.

Example: 'John Doe TOD'
Needs to be: 'John Doe'

On some of our data records we receive, a certain string gets attached to the end of our records (this has been confirmed not on our end) with the following characters 'John Doe TOD'

I am trying to figure out how to use a function called from a stored proc we have to remove the last three letters and the space at the end of the name..

Any help would be appreciated.

I was thinking something like...
I know this is wrong but any help would be great!
I realize the substring is going to start from the beginning, is there a way to start it from the end like a rtrim would..
I can't justify the length of each name so i need to match the 'TOD' at the end for the columns found with that string literal.



SET @TempString = RTRIM(@TempString)
IF SUBSTRING(@TempString,4,4) = ' TOD'
BEGIN
   SET @TempString = SUBSTRING(@Tempstring,4,LEN(@TempString))
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 11:53:01

Not tested but should work well:
set @TempString = replace(rtrim(@TempString)+'kannWeg',' TODkannWeg','')

No, you're never too old to Yak'n'Roll if you're too young to die.

techglider
Starting Member

26 Posts

Posted - 2009-07-23 : 12:09:22

I'm a bit confused..

The ' TOD' needs to be removed from the end.. and not every name will have this, it will only be on a mere sample of the total.. So

The names will not be known on which must be fixed.. And the data in the table will need to stay the same..

This function merely serves as fixing the issue as the sample is loaded into a new format.. ie.. excel

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 12:15:19

I have:
trimmed away the spaces from the right side
added an unambiguous string to the end to be sure ' TOD' in the middle would not be replaced

Now to be sure extend it to:

set @TempString = replace(replace(rtrim(@TempString)+'kannWeg',' TODkannWeg',''),'kannWeg','')

No, you're never too old to Yak'n'Roll if you're too young to die.

techglider
Starting Member

26 Posts

Posted - 2009-07-23 : 12:19:11

Would you mind kind of explaining what each part does? I am still a bit lost.

I'm thinking this is a quick and dirty solution..

But what if the person's last name is TOD..

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-07-23 : 12:23:12

declare @NameTable table (NameString varchar(50))
insert into @NameTable values('John Doe TOD')
insert into @NameTable values('Todd Doe Finkelstein')

select left(NameString, len(NameString)-4)
from @NameTable
where right (NameString, 4) = ' TOD'

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 12:29:53

I will try:
we have sample string 'John Doe TOD '
to be on the save side we trim spaces away from end of the string
we have now 'John Doe TOD'
we add an unambigous string to the end 'kannWeg'
we have now 'John Doe TODkannWeg'
we are replacing ' TODkannWeg' by '' (empty string)
we have now 'John Doe'
to be sure we replace any 'kannWeg' by ''
et voilá

other example is 'John Doe'
we trim and have 'John Doe' anyway
we add 'kannWeg'
we have 'John DoekannWeg'
we are replacing ' TODkannWeg' by ''
we have 'John Doekannweg' because there was no ' TOD'
we are replacing 'kannWeg' by ''
we have 'John Doe'
et voilá

other example
'JOSEF TODDER'
we trim
we add 'kannWeg'
we have 'JOSEF TODDERkannWeg'
we are replacing ' TODkannWeg' by ''
we still have 'JOSEF TODDERkannWeg' because there was no ' TOD' at the end
we are replacing 'kannWeg' by ''
we have 'JOSEF TODDER'
ok?

No, you're never too old to Yak'n'Roll if you're too young to die.

techglider
Starting Member

26 Posts

Posted - 2009-07-23 : 12:30:25

quote:
Originally posted by blindman

declare @NameTable table (NameString varchar(50))
insert into @NameTable values('John Doe TOD')
insert into @NameTable values('Todd Doe Finkelstein')

select left(NameString, len(NameString)-4)
from @NameTable
where right (NameString, 4) = ' TOD'

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

I don't think this will work if there are trailing spaces..
So trailing spaces would have to be trimmed before the right 4 and there is still the issue of if the persons last name is "TOD"

This is not a small sample so even though the possibility of this happening is small it will need to perform fairly well.. Over 20million names

I'm thinking counting breaks would be a more robust way..

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-07-23 : 12:30:55

Whats the @PS variable for in your sample code?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

techglider
Starting Member

26 Posts

Posted - 2009-07-23 : 12:31:37

quote:
Originally posted by webfred

I will try:
we have sample string 'John Doe TOD '
to be on the save side we trim spaces away from end of the string
we have now 'John Doe TOD'
we add an unambigous string to the end 'kannWeg'
we have now 'John Doe TODkannWeg'
we are replacing ' TODkannWeg' by '' (empty string)
we have now 'John Doe'
to be sure we replace any 'kannWeg' by ''
et voilá

other example is 'John Doe'
we trim and have 'John Doe' anyway
we add 'kannWeg'
we have 'John DoekannWeg'
we are replacing ' TODkannWeg' by ''
we have 'John Doekannweg' because there was no ' TOD'
we are replacing 'kannWeg' by ''
we have 'John Doe'
et voilá

other example
'JOSEF TODDER'
we trim
we add 'kannWeg'
we have 'JOSEF TODDERkannWeg'
we are replacing ' TODkannWeg' by ''
we still have 'JOSEF TODDERkannWeg' because there was no ' TOD' at the end
we are replacing 'kannWeg' by ''
we have 'JOSEF TODDER'
ok?

No, you're never too old to Yak'n'Roll if you're too young to die.

What if the persons name is:

"Jason Van Tod"

This would work if the name was
now "Jason Van Tod TOD"

But as mentioned Not every name is going to have the suffix of 'TOD' at the end.. so here is a small example

John Doe TOD
Jason Patricks
Emma Smith
Gina Emmit TOD

There is no way to go around the records without the 'TOD' At the end so all names need to be factored in.

techglider
Starting Member

26 Posts

Posted - 2009-07-23 : 12:32:26

quote:
Originally posted by blindman

Whats the @PS variable for in your sample code?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Don't worry about that.. was working off a similar formatting function.. doesn't need to be there

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 12:35:58

Jason Van Tod is a problem because standard setting is ignore case.
Jason Van Tod TOD isn't a problem.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 12:38:22

John Doe TOD
Jason Patricks
Emma Smith
Gina Emmit TOD

they all will be treated right.

And in blindman's solution you can use rtrim() to make it sure.

No, you're never too old to Yak'n'Roll if you're too young to die.

techglider
Starting Member

26 Posts

Posted - 2009-07-23 : 12:41:10

quote:
Originally posted by webfred

Jason Van Tod is a problem because standard setting is ignore case.
Jason Van Tod TOD isn't a problem.

This is what i am worried about.

I need an instance where ' Jason Van Tod' is safe..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 12:44:19

Jason Van Tod will be the pawn in a game with 20 million players.

No, you're never too old to Yak'n'Roll if you're too young to die.

techglider
Starting Member

26 Posts

Posted - 2009-07-23 : 12:47:57

quote:
Originally posted by webfred

Jason Van Tod will be the pawn in a game with 20 million players.

No, you're never too old to Yak'n'Roll if you're too young to die.

Guess that's the chance we will have to take..

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-23 : 13:20:18

declare @str varchar(50)
set @str = 'Jason Van Tod'

SELECT CASE WHEN UPPER(right(@str,4)) <> ' TOD'
THEN REVERSE(REPLACE(REVERSE(@str),'DOT ',''))
ELSE @str
END

Jim

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-07-23 : 13:30:17

"Van Tod" is a stupid name, and should not be allowed in your database to begin with.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-23 : 13:33:27

I've always hated that guy!

Jim

techglider
Starting Member

26 Posts

Posted - 2009-07-23 : 14:48:19

quote:
Originally posted by jimf

declare @str varchar(50)
set @str = 'Jason Van Tod'

SELECT CASE WHEN UPPER(right(@str,4)) <> ' TOD'
THEN REVERSE(REPLACE(REVERSE(@str),'DOT ',''))
ELSE @str
END

Jim

Still promotes the problem if someones last name is Tod

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 15:00:03

quote:
Originally posted by jimf

declare @str varchar(50)
set @str = 'Jason Van Tod'

SELECT CASE WHEN UPPER(right(@str,4)) <> ' TOD'
THEN REVERSE(REPLACE(REVERSE(@str),'DOT ',''))
ELSE @str
END

Jim

upper isn't helping:
select 1
where upper('xxx') = 'xXX'
gives 1

No, you're never too old to Yak'n'Roll if you're too young to die.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources