Finding the nearest space before a specific count of characters-CodePudding

I'm trying to find any addresses in ADDRESS_1 that is over 20 characters and split it by the nearest space before that 20 character limit into the blank ADDRESS_2 column.

For example:

Address_1: 14256 Canyonville Drive apt 23

would become

Address_1: 14256 Canyonville
Address_2: Drive apt 23

I know this is a super dumb thing to have to do but unfortunately the system I'm working in limits Address_1 to 20 characters and I have to be doing all my data transformations within SQL Server.

CodePudding user response：

Here is a SQL only solution:

select Address_1 as Original_Address_1
     , iif(l <= 20, Address_1, rtrim(substring(Address_1, 1, i))) as Address_1
     , iif(l <= 20, '', ltrim(substring(Address_1, i 1, 200))) as Address_2
  from (
        select Address_1
             , 20 - charindex(' ', reverse(substring(Address_1, 1, 20))) as i
             , len(Address_1) as l
        from @table) a;

OUTPUT

Original_Address_1             Address_1                      Address_2
------------------------------ ------------------------------ ------------------------------
14256 Canyonville Drive apt 23 14256 Canyonville              Drive apt 23
123 Small St                   123 Small St                   
123ReallyBigNoSpaceStreet      123ReallyBigNoSpaceS           treet

(3 rows affected)

CodePudding user response：

You could do something like this

CREATE FUNCTION AddressSlap (@address varchar(256)) RETURNS TABLE 
AS
BEGIN
    IF LEN(@address) < 20
    BEGIN
        SELECT @address AS Address
    END
    ELSE
    BEGIN
        DECLARE @s int
        SELECT @s = 20 - CHARINDEX(' ', REVERSE(LEFT(@address, 20)), 0)

        SELECT LEFT(@address, @s) AS Address
        UNION
        SELECT RIGHT(@address, LEN(@address) - @s)
    END
END

which you can then use with CROSS APPLY.