September 12, 2008 at 9:10 am
Hi there! I'm struggling to split a string.. hope somebody can help me. Here's a description of my problem:
CSCAR009/0001/001-PASPBD-Pedestrian automatic sliding door
I need to access the first two parts of the above string.
CSCAR009/0001/001
PASPBD
I can use left() to get at the first part. It is the same length in all rows.
However this cannot be said for the second. It is a code which changes from row to row, and isn't the same length.
The description on the right also changes and doesn't share the same format even with the same code (user inputted field). I am not interested in this field.
I have tried using the substring function but the third part of the string pops in with the second which messes me up.
I believe what I need is to use a split command on the string to get access to the first 2 parts. What I want is to assign these parts to seperate variables in ASP (the SQL statement will be ran from there too) enabling me to use the data.
I found a guide on inserting a User defined Split function, but am at a loss on how to use it for my purpose. The examples given only show using it in a where clause with an IN performed.
UDF Split:
CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
I rarely post on forums asking for help but this problem has eaten up my afternoon.
Thank you in advance for any help offered.
September 12, 2008 at 1:09 pm
If the -'s are there in every string, you can use CHARINDEX to extract the middle piece.
DECLARE @String varchar(60)
SET @String = 'CSCAR009/0001/001-PASPBD-Pedestrian automatic sliding door'
SELECT LEFT(RIGHT(@String,LEN(@String)-18),(CHARINDEX('-',RIGHT(@String,LEN(@String)-18) ) )-1)
The best thing about this method is that it can be used in any query, replacing @string with your field name.
September 12, 2008 at 11:18 pm
Convert table split function to scalar one.
Add another parameter - @ElementNo and return only value from the table within function with correlated ID.
The you may use it like this:
SELECT dbo.SplitScalar(StringValue, '-', 1), dbo.SplitScalar(StringValue, '-', 2)
FROM ....
_____________
Code for TallyGenerator
September 13, 2008 at 11:26 am
greig.burrows (9/12/2008)
Hi there! I'm struggling to split a string.. hope somebody can help me. Here's a description of my problem:
Ugh... UDF's... While Loops... all good ways to slow your code down. Before I show you how to do this, I need to know a couple of things so I can decide which method to show you...
1. Are you just splitting a single parameter (Sergiy is on the right track for this), or are you splitting a whole column of these?
2. If you're splitting a whole column, I need to know what the primary key of the table is. It would also be handy to have more than one piece of data. Please take a look at the link in my signature below on how to correctly list the data to make it easier for me to help you.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2008 at 1:52 pm
I have a library of UDfs that do exactly what you are looking for and much, much more. however, I sense your aversion to UDFs so I am not sure how you want to go.
There was an article here in SQL Server Central a while ago about "Tally Tables" Using a "Tally" or numbers table will get you where you want to go without loops or UDFs.
Respond back if you want to check out my UDFs.
-M
September 13, 2008 at 2:32 pm
Funny you should bring that article up... 🙂
http://www.sqlservercentral.com/articles/TSQL/62867/
But the desired method to do the split depends on my question... Is this a single parameter being split or does a whole column need to be split?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2008 at 4:42 pm
Oh hell... so long as I'm being self-indulgent...
[font="Arial Black"]Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays[/font]
http://www.sqlservercentral.com/articles/T-SQL/63003/
But, what I'd really like to know is what I've asked a couple of times now... is this to split a single parameter or a whole column of information? The performance of the solution is dependent on that, a lot.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2008 at 6:28 pm
No doubt RBAR is going to occur with UDFs. The tally table idea is going to be a join. The UDF will defintely work. So what do you want? Pretty or Guts & Glory?
-M
September 13, 2008 at 6:38 pm
Performance 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2008 at 6:54 pm
Jeff, performance is good.
But did you see a solution for splitting string with quoted values?
Like in CSV files?
_____________
Code for TallyGenerator
September 13, 2008 at 7:32 pm
Absolutely... what do you want to do? Split a true CSV parameter or a tru CSV column in a poorly formed table that needs a bit of normalization? Heh... or do you want to import a properly formed true CSV file?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2008 at 7:35 pm
Use the Tally table, join to it, then use intrisinc SQL functions because you will have postive indexes to work with. Good luck.
September 13, 2008 at 7:45 pm
Mike DiRenzo (9/13/2008)
Use the Tally table, join to it, then use intrisinc SQL functions because you will have postive indexes to work with. Good luck.
Who's that directed to, Mike?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2008 at 7:48 pm
Greig,
When you get a chance... all I need to know is are you splitting a single parameter or do you need to split a whole column of these? If a whole column, then I need to know what the PK of the table is, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2008 at 8:14 pm
Jeff Moden (9/13/2008)
Absolutely... what do you want to do? Split a true CSV parameter or a tru CSV column in a poorly formed table that needs a bit of normalization? Heh... or do you want to import a properly formed true CSV file?
I've got set of rows coming from "a properly formed true CSV file".
Something like this:
'"Pick-up",,"",1234,"2008-08-24", "2008-08-24","17"", unknown brand"'
'"""Next Day"" delivery",5.00,"9,5"" box",10,"2008-09-01", "2008-09-11","9"" nails"'
You understand, it must come to (for 2nd line):
"Next Day" delivery
5.00
9,5" box
10
2008-09-01
"2008-09-11"
9" nails
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply