Trying to figure a trigger out

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

Transact-SQL (2005)

Trying to figure a trigger out

Author

Topic

Planet_x
Starting Member

15 Posts

Posted - 2010-08-27 : 17:56:05

Trying to figure out a trigger but I just can't understand how to do it, tried looking it up but cant find a similar example. I am trying to break up a string that says something like -

Existing 50mm Carlon to Fire Hall install controller cable to operating switch

I am having no problems breaking the string up and creating a temporary table that splits it into individual strings with their own Position defined as an integer. Now what I can't do is take these individual fields and combine them into another string but only until the length of this new string is not more than 20 characters. The end result would look something like -

string 1 - Existing 50mm Carlon to
string 2 - Fire Hall Install
string 3 - controller cable to
string 4 - operating switch

Below is my SQL code that splits the strings into a new table and attempts to make them into new strings with a 20 char limit. I know there are obviously problems with my loop (setting the final word) as the result is -
Existing 50mm
Carlon to
Fire Hall
install controller
cable to
operating switch

Any help appreciated to get these to 20 characters instead of what they are, thanks!

Below is my SQL code that splits the string into the new table, any help on getting my strings to be put into new one that has a 20 character limit very appreciated!


DECLARE @NextString NVARCHAR(200)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(200)
DECLARE @Delimiter NVARCHAR(40)

DECLARE @Position INT
SET @Position = 0

DECLARE @Results TABLE (Pos int, Label nvarchar(50), Label1 nvarchar(50), Label2 nvarchar(50), Label3 nvarchar(50), Label4 nvarchar(50))

DECLARE @Label1 nvarchar(50)
DECLARE @Label2 nvarchar(50)
DECLARE @Label3 nvarchar(50)
DECLARE @Label4 nvarchar(50)

select @String = Notes from ELP_TC_DuctStub where ELP_TC_DuctStub_ID = '234'

SET @Delimiter = ' '
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SET @Position = @Position +1
insert into @Results(Pos, Label) values(@Position, @NextString) 

SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END 


-- Done Processing, labels split. Begin adding Label field back to Label1, Label2, Label3, Label4

	
	-- set My_Counter to # of Words 
	DECLARE @My_Counter INT
	SELECT @My_Counter = Count (*) from @Results

	-- set My Counter2 to 0 to begin using first word in Label field, then increment
	DECLARE @My_Counter2 INT
	SET @My_Counter2 = 1

	DECLARE @My_Counter3 INT
	SET @My_Counter3 = 1
		


	--begin loop through words (Label field)
	WHILE (@My_Counter >= 0)
	BEGIN
	

		DECLARE @Final_Word nvarchar(200)
		DECLARE @Word1 nvarchar(20)
		DECLARE @Word2 nvarchar(20)
		DECLARE @Word3 nvarchar(20)
		DECLARE @Word4 nvarchar(20)
		

		SET @Final_Word = ''
		
		WHILE (LEN(@Final_Word) < 20)
		begin

		SET @Final_Word = (SELECT Label from @Results where Pos = @My_Counter3) + ' ' + (SELECT Label from @Results where Pos = @My_Counter3 +1)

		SET @My_Counter3 = (@My_Counter3 +2)
		
		END

		--decrement/increment counters
		SET @My_Counter = (@My_Counter -1)
		SET @My_Counter2 = (@My_Counter2 +1)

	END





--SELECT * from @Results

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-27 : 23:02:49

Did you have a solution yet?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-28 : 01:05:30

sorry you expected result is not as per your explanation. you are telling you want to split string into bits of 20 characters but first part itself contains more than 20 chars. can you explain that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Planet_x
Starting Member

15 Posts

Posted - 2010-08-30 : 10:03:47

I need to split the original string into smaller strings of no more than 20 characters is my problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-30 : 10:12:38

[code]
create a udf like this

CREATE FUNCTION SplitStringLen
(@String varchar(8000),
@Len int )
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val char(20))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN LEN(@String) >@Len THEN LEFT(@String,@Len) ELSE @String END,
@String=CASE WHEN LEN(@String) >@Len THEN SUBSTRING(@String,@Len+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

then use it like

SELECT * FROM dbo.SplitStringLen('Existing 50mm Carlon to Fire Hall install controller cable to operating switch',20)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources