i use function how to check is single word:

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 2008 Forums

Transact-SQL (2008)

i use function how to check is single word:

Author

Topic

williamku87
Starting Member

6 Posts

Posted - 2011-06-27 : 04:34:21

[code]declare @desc nvarchar(100)
declare @break int
declare @loop int
declare @output nvarchar(200)

set @break = 10
set @desc = 'this is a test, every 10 character will break'

set @loop = 0
set @output = ''

while @loop <= LEN(@desc)/@break
begin

set @output = @output + SUBSTRING(@desc, (@loop*@break)+1, @break) + ' '

set @loop = @loop + 1
end

select @output

[/code]

output is
this is a test, ever y 10 chara cter will break 

but i need to add extra checking to make sure the word itself doesnt break.

output should be like this
this is a test, every 10 character will break

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-27 : 04:57:59

what if you have a verylongword in the string ?

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-27 : 05:01:51

This should give you an idea. Needs some tidying up for the end of string probably
Also do you want spaces replaced by or as well as.
WHat about punctuation? Would probably need patindex instead or charindex for that.
Could also be made more efficient if needed
declare @desc nvarchar(100)
declare @break int
declare @pos int
declare @spacepos int
declare @output nvarchar(200)
declare @tmp varchar(200)

set @break = 10
set @desc = 'this is a test, every 10 characterddddddddddd will break'

set @pos = 0
set @output = ''

while @pos < LEN(@desc)
begin

set @tmp = SUBSTRING(@desc, @pos+1, @break+1)
set @spacepos = charindex(' ',reverse(@tmp))
if @spacepos = 0
set @tmp = LEFT(@tmp,@break)
else
set @tmp = LEFT(@tmp,@break+1 - @spacepos)

set @output = @output + @tmp + ' '
set @pos = @pos + LEN(@tmp)+1
end

select @output
this is a test, every 10 characterd ddddddddd will break 

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

williamku87
Starting Member

6 Posts

Posted - 2011-06-27 : 05:02:29

quote:
Originally posted by khtan

what if you have a verylongword in the string ?

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

actually i will set my limit, one row is 50.
so one word can't hv 50 length.

williamku87
Starting Member

6 Posts

Posted - 2011-06-27 : 05:06:22

quote:
Originally posted by nigelrivett

This should give you an idea. Needs some tidying up for the end of string probably
Also do you want spaces replaced by or as well as.
WHat about punctuation? Would probably need patindex instead or charindex for that.
Could also be made more efficient if needed
declare @desc nvarchar(100)
declare @break int
declare @pos int
declare @spacepos int
declare @output nvarchar(200)
declare @tmp varchar(200)

set @break = 10
set @desc = 'this is a test, every 10 characterddddddddddd will break'

set @pos = 0
set @output = ''

while @pos < LEN(@desc)
begin

set @tmp = SUBSTRING(@desc, @pos+1, @break+1)
set @spacepos = charindex(' ',reverse(@tmp))
if @spacepos = 0
set @tmp = LEFT(@tmp,@break)
else
set @tmp = LEFT(@tmp,@break+1 - @spacepos)

set @output = @output + @tmp + ' '
set @pos = @pos + LEN(@tmp)+1
end

select @output
this is a test, every 10 characterd ddddddddd will break 

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

i will try. thanks for ur idea.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-27 : 05:06:26

The code I posted splits any long words too. Depends on where your data is coming from but it's dangerous to rely on words being valid.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-27 : 05:16:24

See this
http://weblogs.sqlteam.com/peterl/archive/2009/03/18/Human-readable-string-truncation.aspx

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources