SQL noob needs help from guru with explanation if possible please!

[Richdog]

Hi guys,

Apologies for imposing on your time, but I have an issue which I would please like some help with.

I am now starting SQL for the first time and am getting something of a "crash course" (ie thrown in deep end ).

My task now is to recreate a banking message by writing the code, and then having it output to a text file. This I am able to for the simple parts, but now I have to format a payment senders address so that it adheres to the following banking format rules:

• 4 lines of 35 characters maximum

A sample address would be:

Mrsmithblah12345
Smithincorporated
Smithstreet. 5
2222 Smithsville

BUT, some addresses may be longer than that... so I would need to specify that no matter how long the address, it MUST fit to 4 lines and 35 characters maximum.

By way of example, my code is currently:

SELECT Top 10 '#A10' +CHAR(13)+
'<02>007005' +CHAR(13)+
'<03>'+Test_Ref +CHAR(13)+
'<15>GBP' +CHAR(13)+
'<16>'+CONVERT(varchar( 8 ), GETDATE(), 112) +CHAR(13)+
'<17A>747,15' +CHAR(13)+
'<18>002470' +CHAR(13)+
'<31I>UK3900702210055337528' +CHAR(13)+

So, my question... what code would I need to write in the next line of code (it must follow directly on from what is pasted below) in order to do that?

Thanks for any advice you can give!

 

[cotton31]

Does each of the 4 lines have a max length of 35, or is that the combined max length?

I suppose it depends on where you're getting your data from - is that in a single field and you want to split it into 4, or could it be (in theory) an unlimited number of lines?

Generally you could just do:

LEFT(Address1FieldName, 35) as AddressLine1

 

[Richdog]

Does each of the 4 lines have a max length of 35, or is that the combined max length?

I suppose it depends on where you're getting your data from - is that in a single field and you want to split it into 4, or could it be (in theory) an unlimited number of lines?

Generally you could just do:

LEFT(Address1FieldName, 35) as AddressLine1

Hey man, thanks for the reply!

I showed another friend your code and he said that could also work, but in the end he created a function for me called fn_split_string to do the task:

ALTER FUNCTION [dbo].[fn_split_string]
(@String nvarchar(140))
RETURNS nvarchar(150)
AS
BEGIN

DECLARE @NextString nvarchar(36)
DECLARE @ReturnString nvarchar(150)

SET @ReturnString = ''

WHILE (LEN(@String) > 0)
BEGIN
SET @NextString = SUBSTRING(@String,1,35)
SET @ReturnString = @ReturnString + CASE WHEN @ReturnString = '' THEN '' ELSE CHAR(13) END + @NextString
SET @String = SUBSTRING(@String,36, DATALENGTH(@String)/2)
END
RETURN @ReturnString
END

Seems to work fine! No way I would have figured either of those out myself though myself though... but your solution was definitely more understandable to me so thanks for taking the time to reply.

 

[Makhaira]

Richdog, the only thing to be aware of is that the solution you've picked doesn't use SQL to solve the problem it uses the script. Depending on what the question you've been set is, you might not be answering the right challenge!

 

[Richdog]

Richdog, the only thing to be aware of is that the solution you've picked doesn't use SQL to solve the problem it uses the script. Depending on what the question you've been set is, you might not be answering the right challenge!

The challenge was the result which the script in the end helped me produce, so the end result was thankfully the same.