Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

create a function to remove vowels from a given string Expand / Collapse
Author
Message
Posted Friday, February 15, 2013 8:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:23 AM
Points: 8, Visits: 24
Hi,I am creating a function to remove vowels from a given string but its not working,help me to correct it or give me the correct answer.thank you.

CREATE FUNCTION FN_REMOVEL_VOWELS (@STRING VARCHAR(max))
returns varchar
as
begin
declare @v varchar(max)='AEIOUY' ,@startpoint int =1,@letter varchar(max)
select @letter=SUBSTRING(@v,@startpoint,1)
while @startpoint<LEN(@v)
begin
select @STRING=REPLACE(@STRING,@letter,'')
set @startpoint=@startpoint+1
end
return @STRING
end

SELECT DBO.FN_REMOVEL_VOWELS('INFORMATION SYSTEM')
Post #1420602
Posted Friday, February 15, 2013 9:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 10:24 AM
Points: 577, Visits: 3,414
doesn't this do it?

DECLARE @noVowels [varchar](50) 
SET @noVowels = 'Information System'

SELECT
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(@noVowels,'A','')
,'E','')
,'I','')
,'O','')
,'U','')

Post #1420608
Posted Friday, February 15, 2013 9:06 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
Arrghh David beat me to it!

CREATE FUNCTION dbo.FN_REMOVEL_VOWELS (@STRING VARCHAR(max))
returns varchar(max)
as
begin
RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@STRING, 'A', ''), 'E', ''), 'O', ''), 'U', ''), 'I', '')
END


SELECT DBO.FN_REMOVEL_VOWELS('INFORMATION SYSTEM')

Quick note about your original function. Don't RETURN VARCHAR unless you know your string is never more than 8 characters long.


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1420610
Posted Friday, February 15, 2013 9:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:32 PM
Points: 23,396, Visits: 32,220
I'd do it this way and use it in a CROSS APPLY in the FROM clause:


create function dbo.RemoveVowels(
@pString varchar(max)
)
returns table
as
return (select replace(replace(replace(replace(replace(replace(@pString,'Y',''),'U',''),'O',''),'I',''),'E',''),'A','') NoVowels);
go





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1420613
Posted Friday, February 15, 2013 9:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:23 AM
Points: 8, Visits: 24
thank you all for that new code. Abu Dina, you're right on the return correction. Now when i do that correction the function removes only the 'A' vowel., meaning the increment is not working.how can I organize these statements so that the increment works. thank
Post #1420625
Posted Friday, February 15, 2013 9:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 37,061, Visits: 31,624
byecoliz (2/15/2013)
thank you all for that new code. Abu Dina, you're right on the return correction. Now when i do that correction the function removes only the 'A' vowel., meaning the increment is not working.how can I organize these statements so that the increment works. thank


Forget the code that increments. Do it the way that Lynn suggested. It will be much faster than any scalar function that you can write.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1420627
Posted Friday, February 15, 2013 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 12,915, Visits: 32,074
and a simple cross apply example:
with myCTE(val)
AS
(
SELECT 'Heavy rains that fell across the Upper Mississipp' UNION ALL
SELECT 'i River Basin in the summer of 2007 were responsi' UNION ALL
SELECT 'ble for the Federal Emergency Management Agency (' UNION ALL
SELECT 'FEMA) 1771-DR-IL disaster declaration. These sam' UNION ALL
SELECT 'e rains caused significant flooding in southeaste' UNION ALL
SELECT 'rn Minnesota, eastern Iowa, southern Wisconsin an' UNION ALL
SELECT 'd northern Illinois. Large portions of northern ' UNION ALL
SELECT 'Illinois received between 125 and 175 inches of r' UNION ALL
SELECT 'ain during this period, and this, combined '
)
select val,a.val2
FROM MyCTE
CROSS APPLY (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(val, 'A', ''), 'E', ''), 'O', ''), 'U', ''), 'I', '') as val2
) a



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1420629
Posted Friday, February 15, 2013 9:57 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
Take the others' advice and ditch the loop for the inline table valued function.

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1420645
Posted Friday, February 15, 2013 10:01 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
Jeff Moden (2/15/2013)
byecoliz (2/15/2013)
thank you all for that new code. Abu Dina, you're right on the return correction. Now when i do that correction the function removes only the 'A' vowel., meaning the increment is not working.how can I organize these statements so that the increment works. thank


Forget the code that increments. Do it the way that Lynn suggested. It will be much faster than any scalar function that you can write.


Unless it's a SQL CLR C# UDF surely?

Well I guess it depends on what the UDF needs to do. I recently posted a question about a SQL scalar function I had which contained over 200 IF statements. It was performing like a pig and I couldn't see a way of converting it to an iTVF so I write it in C#. The result? the C# version was 100 times faster than the SQL equivalent.


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1420648
Posted Friday, February 15, 2013 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:23 AM
Points: 8, Visits: 24
Thank you all,I will take your advice.
regards
Post #1420655
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse