Split string preserving word

bobdragon12 · January 6, 2020, 11:21am

HI
I need some help splitting an address string at 30 Char or the closest space before 30 . I have found the below which works but get errors if i remove the "where DeliveryAddress = 2651"

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any help Greatly appreciated in finding a solution
declare @strs nvarchar(max)
set @strs = (select distinct deliveryaddresses.address from DeliveryAddresses where DeliveryAddress = 2651)
set @strs = reverse(@strs)
select reverse(@strs) String,
reverse(right(@strs,len(@strs) - charindex(' ',@strs,30))) Description1,
ltrim(reverse(left(@strs,charindex(' ',@strs,30)))) Description2

Wim_Leys · January 6, 2020, 1:05pm

@strs is a string variable. It can store ONE string.

select distinct deliveryaddresses.address
from DeliveryAddresses 
where DeliveryAddress = 2651

Due to the WHERE clause, the query will return (at most) ONE string. @strs can store that string.

Without the WHERE clause,

select distinct deliveryaddresses.address
from DeliveryAddresses

will return as many strings as there are records with a different address. @strs can not store a data-set with many strings.

bobdragon12 · January 6, 2020, 1:22pm

HI , I understand where I'm going wrong . I only showed the WHERE to show the query works for a single string . What I lack is the knowledge of how to push multiple strings through and split them .

Any assistance appreciated

harishgg1 · January 6, 2020, 1:43pm

if you want to push multliple strings

please try ... CROSS APPLY

Wim_Leys · January 6, 2020, 1:46pm

I'm not sure what you want.
Something in the line of this?

select distinct 
	deliveryaddresses.address
	, reverse(@strs) String
	, reverse(right(@strs, len(@strs) - charindex(' ', @strs, 30))) Description1
	, ltrim(reverse(left(@strs, charindex(' ', @strs, 30)))) Description2
from DeliveryAddresses
--where DeliveryAddress = 2651

bobdragon12 · January 6, 2020, 2:06pm

HI , yes but you still need to declare the @strs , I tried the below but got

select distinct 
	deliveryaddresses.address
	, reverse(	deliveryaddresses.address) String
	, reverse(right(	deliveryaddresses.address, len(	deliveryaddresses.address) - charindex(' ', 	deliveryaddresses.address, 30))) Description1
	, ltrim(reverse(left(	deliveryaddresses.address, charindex(' ', 	deliveryaddresses.address, 30)))) Description2
from DeliveryAddresses
--where DeliveryAddress = 2651

Msg 536, Level 16, State 4, Line 1
Invalid length parameter passed to the RIGHT function.

Wim_Leys · January 6, 2020, 2:30pm

HI , yes but you still need to declare the @strs

I see no reason for that. Why do you think you need a variable?
Can you give us some background information? What is the purpose of your project? What will you do with the result of all those string functions?

It would help if you could provide us with the string that gives the error.

Invalid length parameter passed to the RIGHT function .

right(deliveryaddresses.address, 
      len(deliveryaddresses.address) - charindex(' ', deliveryaddresses.address, 30)))

I bet len(deliveryaddresses.address) - charindex(' ', deliveryaddresses.address, 30)) is negative for at least one address.

Run this query:

select distinct 
	deliveryaddresses.address
	, reverse(	deliveryaddresses.address) String
	, len(deliveryaddresses.address) - charindex(' ', deliveryaddresses.address, 30)) 
from DeliveryAddresses
WHERE len(deliveryaddresses.address) - charindex(' ', deliveryaddresses.address, 30)) < 0

ScottPletcher · January 6, 2020, 3:00pm

SELECT 
    query1.address, 
    LEFT(query1.address, ca1.description1_length) AS Description1,
    LTRIM(SUBSTRING(query1.address, ca1.description1_length + 2, 500)) AS Description2
FROM (
    SELECT DISTINCT DA.address
    FROM dbo.DeliveryAddresses DA
) AS query1
CROSS APPLY (
    SELECT 30 - CHARINDEX(' ', REVERSE(LEFT(query1.address, 30))) AS description1_length
) AS ca1

bobdragon12 · January 6, 2020, 3:14pm

That makes sense , I added a case statment to ignote the negatives , just not sure how to reverse it all back round

case when len(deliveryaddresses.address) - charindex(' ', deliveryaddresses.address, 30) < 0 then '' else reverse(right( deliveryaddresses.address, len( deliveryaddresses.address) - charindex(' ', deliveryaddresses.address, 30))) end as Description1

so its legible

--    declare @strs nvarchar(max)
    ---set @strs = (select distinct deliveryaddresses.address from DeliveryAddresses where DeliveryAddress = 2654)

  --  set @strs = reverse(@strs)
    select (deliveryaddresses.address) String,
     case when  len(deliveryaddresses.address) - charindex(' ', deliveryaddresses.address, 30) < 0 then '' else  reverse(right(	deliveryaddresses.address, len(	deliveryaddresses.address) - charindex(' ', 	deliveryaddresses.address, 30))) end as  Description1 ,
    ltrim(reverse(left(deliveryaddresses.address,charindex(' ',deliveryaddresses.address,30)))) Description2
    --,REVERSE Description2 

    from DeliveryAddresses

harishgg1 · January 6, 2020, 3:48pm

hi

Reverse function ( dont know which version of SQL Server it starts 2016 or 2017 )
https://docs.microsoft.com/en-us/sql/t-sql/functions/reverse-transact-sql

harishgg1 · January 6, 2020, 3:51pm

Reverse String In SQL Server Without REVERSE Function

CREATE function StringReverse(@inputstring varchar(max))
returns varchar(max)
AS
BEGIN
DECLARE @i int,
@Result varchar(max)
SET @Result=''
SET @i = 1
WHILE @i <= LEN(@inputstring)
BEGIN
SET @Result = SUBSTRING(@inputstring,@i,1) + @Result
SET @i=@i + 1
END
RETURN @Result
END

select dbo.StringReverse('xyz123abc')
cba321zyx

bobdragon12 · January 6, 2020, 4:14pm

OK I manged to reverse the reverse , but i have a problem , when the string is less than 30 char it put the address in Description 2 which is incorrect. when the string is over 30 it puts the lead in Description 1 and the tail in Description2 . also when the string is less than 30char it cuts the first Char

select distinct 
	deliveryaddresses.address
	, reverse(	deliveryaddresses.address) String
	,  case when  len(deliveryaddresses.address) - charindex(' ', deliveryaddresses.address, 30) < 0 then '' else  reverse(reverse(right(	deliveryaddresses.address, len(	deliveryaddresses.address) - charindex(' ', 	deliveryaddresses.address, 30)))) end  as  Description1
	, reverse(ltrim(reverse(left(	deliveryaddresses.address, charindex(' ', 	deliveryaddresses.address, 30))))) Description2
from DeliveryAddresses

harishgg1 · January 6, 2020, 4:23pm

hi

Description 2 Description 1 ....
i think its a case of your TSQL .. LOGIC
....

Also when less than 30 cut the first char part
i think it has to do with your TSQL code ...

ScottPletcher · January 6, 2020, 5:34pm

Did you try my version of a solution?

bobdragon12 · January 6, 2020, 7:18pm

Hi , it is but I need to push multiple lines through so I can't push

@str = (select deliveryaddresses.address from deliveryaddresses) through the variable as it returns more than one result

Wim_Leys · January 6, 2020, 8:48pm

Can you explain what you want to do with Description1 and Description2?
Are you planning to UPDATE a table, INSERT Description1 and Description2 in another table, ...?
I have absolutely no idea what you mean by "so I can't push".

A solution without @str

select distinct 
	deliveryaddresses.address
	, TRIM(LEFT(LEFT(deliveryaddresses.address, 30), len(LEFT(deliveryaddresses.address, 30)) - charindex(' ', reverse(LEFT(deliveryaddresses.address, 30))))) Description1
	, TRIM(SUBSTRING(deliveryaddresses.address, len( LEFT(deliveryaddresses.address, 30)) - charindex(' ', reverse(LEFT(deliveryaddresses.address, 30))) + 1, 100)) Description2 
from DeliveryAddresses

yosiasz · January 6, 2020, 8:52pm

so you want to standardize a bunch of random text that look like address into a well formatted address with random commas and space in between that might or might not exist.

bobdragon12 · January 6, 2020, 9:33pm

Ok , so our ERP has a single string box for delivery address data . We then export order via CSV and the receivers of this data have just piped up and said that we are only using 1 of the available columns and that we have been truncating data for 10years

bobdragon12 · January 7, 2020, 8:16pm

Im unable to change ERP as it's not my software I can't add tables to the DB for the same reason and only need the string splitting for one client output . Was hoping for an easy route

bobdragon12 · January 9, 2020, 1:56pm

this is a winner ,

SELECT 
query1.DeliveryAddress,
    query1.address, 
    LEFT(query1.address, ca1.description1_length) AS Address1,
    LTRIM(SUBSTRING(query1.address, ca1.description1_length + 2, 500)) AS Address2
FROM (
    SELECT DISTINCT DA.address,DeliveryAddress
    FROM dbo.DeliveryAddresses DA
) AS query1
CROSS APPLY (
    SELECT 30 - CHARINDEX(' ', REVERSE(LEFT(query1.address, 30))) AS description1_length
) AS ca1