SQL 2000: Unknown character delimiter in DB

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

SQL 2000: Unknown character delimiter in DB

Author

Topic

cflevaris
Starting Member

2 Posts

Posted - 2007-12-18 : 13:34:02

I am having a problem identifying a delimiter in a database and I am hoping someone can suggest a way for me to find out what it is.

I have an application that enters information into a SQL db. In one field, it takes several serial numbers from parts that get shipped out. The interface looks like this:

Part Number Serial Number(s)
<Field> (20char) <Field> <- This is large&can have many char

Typically this information is entered via a handheld scanner but is sometimes manually entered.

When I look at the data in the database, I can see all the information that was gathered from the box that was scanned or copied. When you look at a particular record about a ship order, you see that each line item on the shipping order has just 1 part number in one variable but several serial numbers in the other, like this:
ShipNum Part Number QTY Serial Number
101 ABC123 4 123 124 125 126
101 BCD12 3 AB1 AB2 AB7
102 CDE12 1 AA1

This is what it looks like in my SQL database.
I would like to parse this data. I have written code that all I have to do is offer the delimiter and it will rearrange the above output as:
ShipNum Part Number Serial Number
101 ABC123 123
101 ABC123 124
101 ABC123 125

And so on. What I can't get is the delimiter! If I send the output from my SELECT command to a csv file, the output looks like this:
101,ABC123,123
124
125
101,BCD12,AB1
AB2
AB7
102,CDE12,AA1

And so on. That's right, no comma between the Serial Numbers.
If I output my select command to the screen it looks like this:
ShipNum Part Number Serial Number
101 ABC123 123 124 125 126
101 BCD12 AB1 AB2 AB7
102 CDE12 AA1

Neither output to csv or cut and paste from the output screen does not reveal to me what the delimiter is.
Does anyone know what it might be or a way to just output raw data so that I can snag the delimiter right from the output? Does anyone have any other ideas how to handle this?
Thanks!
Constantine

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-18 : 14:13:53

[code]DECLARE @Sample VARCHAR(999)

SET @Sample = '101,ABC123,123
124
125
101,BCD12,AB1
AB2
AB7
102,CDE12,AA1'

SELECT SUBSTRING(@Sample, m.number, 1) AS Character,
ASCII(SUBSTRING(@Sample, m.number, 1)) AS Ascii
FROM master..spt_values AS m
WHERE m.type = 'p'
AND m.number > 0[/code]

E 12°55'05.25"
N 56°04'39.16"

cflevaris
Starting Member

2 Posts

Posted - 2007-12-18 : 16:57:58

I will see if this reveals the hidden characters, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources