String manipulation
-
Hi,
This is my problem, i'm pulling in rows from a text file that get loaded in to a single column per line in the text file, so i end up with something like this for two rows:
xxxx;1234;yyyy;3456
yyyy;1234;;3456
what i'm trying to do is pull each bit of information out on its own in to seperate columns in another table, i'm using a combination of patindex and substring to seperate out each bit of info, the delimiter is always ; but its getting pretty messy. Heres an example that does the first 2 bits of data:
select left(textin,patindex('%;%',TextIn)-1) as a,
substring(textin,
patindex('%;%',TextIn)+1,
patindex('%;%',substring(textin,patindex('%;%',TextIn)+1,50))-1) as b
from tbtextin
As you can imagine it starts to grow a bit when you have 10 bits of data in one row.
Has anyone got a nicer way of doing it?
Growing old is mandatory, growing up is optional -
If you have SQL2K then you write a udf to extract the data, I believe there are examples on this site, try a search. There may be other solutions too.
If you need an alternative then post again and someone will post something.
In the mean time I'll try to think of something myself.
Far away is close at hand in the images of elsewhere.
Anon. -
How are you 'pulling in the data' DTS / BCP ?
Far away is close at hand in the images of elsewhere.
Anon. -
its loaded in from a web app, the user selects the file they want to upload, it then puts each line from that file in to the table.
Growing old is mandatory, growing up is optional -
Can the app split each line and then insert the 10 columns. How is the app inserting the data?
Far away is close at hand in the images of elsewhere.
Anon. -
The app reads in one line, inserts it in to the table, reads the next line etc. Its just the splitting out of each chunk of data from each line. I was thinking about doing it in the code of the web app but thought it might be easier and quicker to run it on the DB server.
Growing old is mandatory, growing up is optional -
In your case, I would do it in the app. SQL has no parsing ability, only string slicing. To get SQL to do you would have to write udf or write repeated code to slice the data
DECLARE @inp varchar(100)
DECLARE @idx int
DECLARE @col1 varchar(10),@col2 varchar(10),@col3 varchar(10),@col4 varchar(10),@col5 varchar(10)
DECLARE @col6 varchar(10),@col7 varchar(10),@col8 varchar(10),@col9 varchar(10),@col10 varchar(10)
SET @inp = 'xxxx;1234;yyyy;3456'
SET @idx = CHARINDEX(';',@inp)
IF @idx > 0
BEGIN
SET @col1 = LEFT(@inp,@idx-1)
SET @inp = SUBSTRING(@inp,@idx+1,LEN(@inp))
END
ELSE
BEGIN
SET @col1 = @inp
SET @inp = ''
END
SET @idx = CHARINDEX(';',@inp)
IF @idx > 0
BEGIN
SET @col2 = LEFT(@inp,@idx-1)
SET @inp = SUBSTRING(@inp,@idx+1,LEN(@inp))
END
ELSE
BEGIN
SET @col2 = @inp
SET @inp = ''
END
SET @idx = CHARINDEX(';',@inp)
IF @idx > 0
BEGIN
SET @col3 = LEFT(@inp,@idx-1)
SET @inp = SUBSTRING(@inp,@idx+1,LEN(@inp))
END
ELSE
BEGIN
SET @col3 = @inp
SET @inp = ''
END
SET @idx = CHARINDEX(';',@inp)
IF @idx > 0
BEGIN
SET @col4 = LEFT(@inp,@idx-1)
SET @inp = SUBSTRING(@inp,@idx+1,LEN(@inp))
END
ELSE
BEGIN
SET @col4 = @inp
SET @inp = ''
END
SELECT @col1,@col2,@col3,@col4,@col5
Far away is close at hand in the images of elsewhere.
Anon. -
Thank you very much indeed for your help, i think you've pulled me out of a biggish hole there.
Growing old is mandatory, growing up is optional
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply