0

I have a table in my SQL Server database with a column of type NTEXT. The column holds address information, like this:

"Company name
Street + number
Postalcode + City
Country"

Sometimes the country is not there, but the first 3 lines are always there.

How would I go about selecting only line 3?

Each line is separated with CR + LF (\r\n)

I need this as part of a SQL Server stored procedure and the column I use is called RecipientAddress

The reason why I need this to be done within an SP is that I use the data to create a Crystal Report.

Or is there a way to do this within a formula in Crystal Reports?

EDIT: The datatypes used for the fields cannot be changed at the moment, since fields are part of an ERP system, where we are not able to change the datatypes.

Kenneth_H
  • 141
  • 2
  • 13
  • 2
    Can you not change the database structure so each of those values is in a different field? – DavidG Aug 03 '15 at 12:01
  • `ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) – marc_s Aug 03 '15 at 12:24
  • @marc_s The reason why we use `ntext` is that the report gets data from an existing ERP system that uses these datatypes, so I cannot change the datatypes used. This has to come from the developer of the ERP system. @DavidG Due to the structure of the current table in the ERP system, I cannot change it to use seperate fields for this. – Kenneth_H Aug 03 '15 at 12:40
  • 1
    Perhaps you should write a nasty letter to the software vendor. Using NTEXT and storing multiple data items in a single field is such a rookie mistake. The software vendor should know better. – Dave Mason Aug 03 '15 at 12:53
  • They already know that they will have issues with the newer versions, but for the time being, `NTEXT` is what I have to work with and seperating a string would be the same regardless of which datatype I use. – Kenneth_H Aug 03 '15 at 12:58
  • You have all the power you need to parse the string with CAST(), PATINDEX() and SUBSTRING(). – Tab Alleman Aug 03 '15 at 12:59
  • possible duplicate of [How do I split a string so I can access item x](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) – Tab Alleman Aug 03 '15 at 13:03

1 Answers1

0

I didn't use patindex because I suppose you are not using SS2014/16

It works on a set of addresses but can be change to work on only 1 value at a time using a variable

I used 2 CTE because it is easier to read and write the query this way. You can use intermediary variables instead if you work on only 1 address in a variable.

Code below can be tested in Management Studio (it creates 1 with country and 1 without country):

declare @delim varchar(10) = char(13)+char(10)
declare @table table(ID int, Address varchar(max))
insert into @table(ID, Address) values(0, 'Company name1
Street 1000
92345 City
Country')
insert into @table(ID, Address) values(1, 'Company name
Street 1000
92345 City')

; With row_start as(
    Select ID, Address, pos_start = charindex(@delim, Address, charindex(@delim, Address, 0)+1)+len(@delim)
    From @table
) 
, row_end as (
    Select ID, Address, pos_start, pos_end = charindex(@delim, Address,pos_start+1)
    From row_start
)
Select ID, Address
    , Zip_City = substring(Address, pos_start, (case when pos_end = 0 then len(Address)+1 else pos_end end) - pos_start)
From row_end
Julien Vavasseur
  • 3,854
  • 1
  • 20
  • 29