space - 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

space

Author

Topic

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-10-24 : 03:19:25

hi everybody

how to remove space left to right and right to left

if i give limit >60 for first 60 character; limit 60< second 60 character

result would be check if space at 60 character if yes remove and go the 59 character check then space remove and 58 character check if there is charater then display

as well as after 60 character to till 120 for right space

what is sql code
pls post it

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-24 : 03:24:00

please post sample data and required result

KH
[spoiler]Time is always against us[/spoiler]

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-10-24 : 03:40:25

KINDLY NEED HELP.. CODE AS GIVEN BELOW

DECLARE @REV VARCHAR(MAX);
declare @v_firstsixty varchar(max);
declare @v_aftersixty varchar(max);
declare @Res1 varchar(500);
declare @v_len int;
declare @v_f2 int;
SELECT @Res = replace(ISNULL(@Res,''),',','')
set @v_len= LEN(@Res + @strCents)

if @flag = 1
SET @REV = @Res + @strCents
set @Res1 = (@REV)
if @v_len <= 60
begin

--set CHARINDEX( @res , @strcents) >=0
SET @REV = SUBSTRING(@Res + @strCents ,1,60)
set @Res1 = (@REV)
end
else
begin

SET @REV = REVERSE(SUBSTRING(@Res + @strCents,1,60))
set @v_firstsixty=REVERSE(SUBSTRING(@REV,CHARINDEX('',@REV)+1,LEN(@REV)))
set @Res1 = @v_firstsixty

end

if @flag = 2

begin
set @REV = REVERSE(SUBSTRING(@Res+@strCents,1,60))
set @v_f2= len(@rev)--len(REVERSE(SUBSTRING(@REV,CHARINDEX('',@REV)+1,60)))
set @v_aftersixty=SUBSTRING(@Res + @strCents,60,@v_len)--LEN(@StrFinal + ' '+ @strCents))
set @v_aftersixty=SUBSTRING(@Res+@strCents,@v_f2+2,LEN(@Res+@strCents))

set @Res1 = @v_aftersixty
end

if @flag = 0
BEGIN
SET @Res1 = @Res + @strCents
end
-----*******************************************************
SET @Res1 = LTRIM ( RTRIM(@Res1 ))
RETURN @Res1
END

EXECUTE
------
select dbo.fn_spellnumber('12,34,56,789.35','L',1,0)
select dbo.fn_spellnumber('12,34,56,789.35','L',1,1)
select dbo.fn_spellnumber('12,34,56,789.35','L',1,2)

RESULT 1: Twelve Crore Thirty Four Lakh Fifty Six Thousand Seven Hundred And Eighty Nine Thirty Five Cents (OK)
RESULT 2 : Twelve Crore Thirty Four Lakh Fifty Six Thousand Seven Hundr
RESULT 3: d And Eighty Nine Thirty Five Cents

RESULT WOULD BE IN ROUND FIGURE

PLS.........

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-24 : 03:48:09

if you are looking for a function to convert number to words, search this site

example :
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11157
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86067
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151482

and lots more

KH
[spoiler]Time is always against us[/spoiler]

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-10-24 : 04:48:36

thanks sir, but remove left and right space using while loop

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 08:58:33

quote:
Originally posted by rajnidas

thanks sir, but remove left and right space using while loop

use LTRIm and RTRIm for that. no need of loop

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources