Split string preserving word

Transact-SQL
24

well I thought it was , its still splits the odd word . not sure why though

image
image904×59 2.48 KB

image
image885×35 1.44 KB

25

Hmm, I'll take a look when I get a chance. You need to supply the sample data to insure full testing :slight_smile: .

26

here is the data you never provided

create table #shimsham(address nvarchar(1500) )

insert into #shimsham
select '6 THE GREEN STUBBINGTON' union
select 'UNIT 6 YOUNGS INDUSTRIAL ESTATE STANBRIDGE ROAD' union
select 'MARSHALL WAY COMMERCE PARK' union
select 'EYTON GRANGE' union
select '74 TO 76 COMMERCIAL STREET YSTRADGYNLAIS' union
select 'TYRINGHAM TYRINGHAM' union
select 'SSALTS MILL SALTAIRE' union
select '6 EAST STREETBreak WIMBORNEBreak DORSETBreak' union
select 'TIR-Y-BERTH INDUSTRIAL ESTATE NEW ROAD' union
SELECT '9 MEON CLOSE' union
select 'UNIT 5, PAULA WORKS CHARNOCK ROAD AINTREE' 

drop table #shimsham
27

HI , data as below

USE training 
 
CREATE TABLE #addresses 
(
	address VARCHAR(200)

 
)
 
INSERT INTO #addresses
(address)
values
('SERCHEM LTD  HEATHERSET'),
('25 FLAMINGO CLOSE  WALDERSLADE'),
('129 The Ridings  Ockbrook'),
('THE OLD RAILWAY  NEWTOWN'),
('SANDALWOOD  80 SUNDERTON LANE')

select* from #addresses 


 SELECT 

  
  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
    FROM #addresses  DA
) AS query1
CROSS APPLY (
    SELECT 30 - CHARINDEX(' ', REVERSE(LEFT(query1.address, 30))) AS description1_length
) AS ca1
28

Oops, D'OH, I forgot to check for the entire address being less than or equal to 30 chars long. Here's the corrected query:

SELECT   
    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
    FROM #addresses DA
) AS query1
CROSS APPLY (
    SELECT CASE WHEN LEN(query1.address) <= 30 THEN 30
        ELSE 30 - CHARINDEX(' ', REVERSE(LEFT(query1.address, 30))) END AS description1_length
) AS ca1
1 Like
29

You sir are a :star2:. I will check it as soon as I get to the office tomorrow

30

HI , many thanks this is just what i was trying to achieve