SQL noob needs help from guru with explanation. :)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL noob needs help from guru with explanation. :)

Author

Topic

Richdog
Starting Member

2 Posts

Posted - 2013-05-28 : 05:43:42

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 :D).

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:

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! :)

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-28 : 05:50:15

like this?

LEFT( [your column] , 35)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 06:02:53

which column has the address part? Whats the maximum length of the field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Richdog
Starting Member

2 Posts

Posted - 2013-05-29 : 05:28:55

quote:
Originally posted by waterduck

like this?

LEFT( [your column] , 35)

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:

quote:
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. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources