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.
| Author |
Topic |
|
Feurich
Starting Member
4 Posts |
 Posted - 2007-05-29 : 17:55:59
|
Hi I am new to the t-SQL stuff and am having a hard time writting a UDF to split up a address field. The field is being split by CHAR(13) CRLF.And I want to create a UDF that returns a table with all the separated field of the envelopaddress field in it.For one thing is there a way of debugging T-SQL Code from within the Query Analyzer? And if anyone has pointers for me on how to split the envelopaddress field please post it.I will be very gratefull.Eric Feurich |
|
|
kdyer
Yak Posting Veteran
53 Posts |
Posted - 2007-05-29 : 18:23:35
|
| You may need to include both:CHAR(13)as well as: CHAR(10)http://asciitable.com may also help you with char values too.One thing to keep in mind: UNIX uses Line Feed, Mac Uses Carriage Return and Dos/Windows uses both.Thanks,Kent |
 |
|
|
Feurich
Starting Member
4 Posts |
Posted - 2007-05-29 : 18:51:44
|
| Hi,Can someone explain to me why I get the error"Must declare the variable '@FullString'"When I remove the @ in the insert into line this error is gone.What is the difference between FullString and @FullString?Eric------ User Defined Function to Split up a Address Field where the address parts are seperated by CHAR(13) + CHAR(10)--- Input: SchoolID. The school id ---USE StichtingNOB_ScholenGOCREATE FUNCTION dbo.Split (@SchoolID varchar(5))RETURNS @OutTable TABLE( LineNum int NOT NULL PRIMARY KEY, Line varchar(15) NOT NULL)ASBEGINDECLARE @LineNum as int, @Line varchar(16), @FullString as varchar(255),@LinePos as int, @StartPos int, @SepChars as varchar(2)INSERT INTO @FullString SELECT envelopadres FROM tblAdressen WHERE SCHOOL_ID= @SchoolIDSET @LineNum = 1SET @StartPos = 1SET @SepChars = CHAR(13) + CHAR(10)WHILE @FullString <> '' BEGIN -- Get the lineposition of the CRLF in the string SET @LinePos = CHARINDEX(@SepChars, @FullString) -- Get the first part of the AdresString SET @Line = SUBSTRING(@FullString,@StartPos,@LinePos) -- Populate the OutTable with the values from @FullString INSERT INTO @OutTable (LineNum, Line)VALUES (@LineNum, @Line) --- Decrease the FullString to the new length SET @FullString = LEN(@FullString) - @LinePos -- Update the Starting positon to the last LinePosition plus one for the next adresline in the string. SET @StartPos = @LinePos + 1 -- Increase the Loop counter and the Table line counter. SET @LineNum = @LineNum + 1 ENDRETURNENDGO |
|
|
|
Feurich
Starting Member
4 Posts |
Posted - 2007-05-30 : 08:27:38
|
| Ok, I have it some what running but now I het the error:" String or Binary Data would be truncated".I looked on the internet but the solution seems to switch off the ANSI Warnings.I don't know much about this but is this wise?Eric |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-30 : 08:34:19
|
what is the length of the string you pass into the function ? try increasing this @SchoolID varchar(8000)
KH |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-05-30 : 08:44:02
|
| It's a great idea to store only a single piece of information in each field, thereby avoiding altogether the effort that goes into solving problems like this. Have a look into normalisation: [url]http://en.wikipedia.org/wiki/First_normal_form[/url]Mark |
|
|
|
Feurich
Starting Member
4 Posts |
Posted - 2007-05-31 : 04:56:49
|
| Thanks for the info.To reply to Mark,The address field(1 field for the whole address) is created for the benefit of the customer and now the want the customer wants to split the data. As you probably know it's what the customer wants :-)TO KH, I tried increasing the length of @SchoolID to 8000 but the same error stays.Eric |
|
|
|
|
|
|
|
|
Address Split