Splitting a column into 2

1

Hello, I am trying to split an item description column into 2 fields. Currently the field is maxed at 40 characters. The new columns are 30 characters. It looks something like this:
ItemNO Descript
A This description is going to be over 30 characters in length
B this is less than 30

Will look something like

Itemno Descript1 descript2
A This description is going to be over 30 characters in length
B This is less than 30
I need to split it, without breaking a word in half. So if the 30th Character is in a word, i need it to stop at the beginning of the word. There is nothing in the description field that i use as a reference for CHARINDEX. I've tried another method i found using substring -CHARINDEX(' ', etc , but alls that did was go back the number of characters it took to find a blank space.

If someone can help with a query that will get the first part of 30 characters in length but not cutting a word in half, i can then use that and REPLACE to remove that part and finish up the second have of the description field into the 2nd new field.

I know this is no small task and i thank you wayyyyy in advance

3

declare @text as varchar(100)
select @text = 'Hi My name is viggneshwar, I am working in Copany as Private'

declare @words as TABLE (
pos smallint,
value varchar(8000)
)

DECLARE @delims varchar(10)
SET @delims = ' '
DECLARE @pos smallint, @i smallint, @s varchar(8000)

SET @pos = 1

WHILE @pos < LEN(@text)
AND CHARINDEX(SUBSTRING(@text, @pos, 1), @delims) > 0
SET @pos = @pos + 1

WHILE @pos <= LEN(@text)
BEGIN
SET @i = PATINDEX('%[' + @delims + ']%',
SUBSTRING(@text, @pos, len(@text) - @pos + 1))
IF @i > 0
BEGIN
SET @i = @i + @pos - 1
IF @i > @pos
BEGIN
-- @i now holds the earliest delimiter in the string
SET @s = SUBSTRING(@text, @pos, @i - @pos)
INSERT INTO @words
VALUES (@pos, @s)
END
SET @pos = @i + 1

     WHILE @pos < LEN(@text) 
        AND CHARINDEX(SUBSTRING(@text, @pos, 1), @delims) > 0 
        SET @pos = @pos + 1 
  END
  ELSE 
  BEGIN
     SET @s = SUBSTRING(@text, @pos, LEN(@text) - @pos + 1)
     INSERT INTO @words 
     VALUES (@pos, @s) SET @pos = LEN(@text) + 1
  END

END

select value + ' '
from @words
where pos+len(value) <= 40
for xml path('')

 select value  + ' '

from @words
where pos+len(value) > 40
for xml path('')

4

viggneshwar,

Thank you so much for the reply. I see that it does work with your example. However, forgive me as this is beyond my current skills. How can i get this to work with my specific table? Fields Itemno and Descript. It needs to leave Itemno how it is, but spilt the Descript field.

5

Please explain your scenario and situation.
you have to do this task only on insertion?

This is done on insertion or update then you can use this query for each row pass the value to @text and you will get output

6

Wouldn't something like this be enough?

CREATE TABLE #TEMP_TABLE
(
	MyText	varchar(40) NOT NULL
)

INSERT INTO #TEMP_TABLE(MyText)
--       ....!....1....!....2....!....3....!....4
SELECT	'This is less than 30 chars' UNION ALL
SELECT	'This is less than more than 30 chars' UNION ALL
SELECT	'Exactly 30 chars xxxx xxxx xxx' UNION ALL
SELECT	'Edge condition 1 xxxx xxxx yyyy' UNION ALL
SELECT	'Edge condition 2 xxxx xxxx xxx y' UNION ALL
SELECT	'Edge condition 3 xxxx xxxx xx yy' UNION ALL
SELECT	'NoSpacesInFirst30xxxxxxxxxxxxxy yyy'
--
;WITH SPLIT_CTE
AS
(
	SELECT	MyText,
		[T_Offset] = CASE WHEN LEN(MyText) <= 30
				THEN LEN(MyText)
				ELSE COALESCE(30 + 1 - NullIf(CHARINDEX(' ', REVERSE(LEFT(MyText, 30+1))), 0), 30)
				END
	FROM	#TEMP_TABLE
)
SELECT	
	[....!....1....!....2....!....3....!....4] = MyText,
	[LeftPart] = LEFT(MyText, T_Offset),
	[RightPart] = NullIf(LTrim(SUBSTRING(MyText, T_Offset+1, LEN(MyText))), '')
FROM	SPLIT_CTE
--
DROP TABLE #TEMP_TABLE
GO
--
-- Results
....!....1....!....2....!....3....!....4 LeftPart                       RightPart
---------------------------------------- ------------------------------ ---------
This is less than 30 chars               This is less than 30 chars     NULL
This is less than more than 30 chars     This is less than more than 30 chars
Exactly 30 chars xxxx xxxx xxx           Exactly 30 chars xxxx xxxx xxx NULL
Edge condition 1 xxxx xxxx yyyy          Edge condition 1 xxxx xxxx     yyyy
Edge condition 2 xxxx xxxx xxx y         Edge condition 2 xxxx xxxx xxx y
Edge condition 3 xxxx xxxx xx yy         Edge condition 3 xxxx xxxx xx  yy
NoSpacesInFirst30xxxxxxxxxxxxxy yyy      NoSpacesInFirst30xxxxxxxxxxxxx y yyy