Finding characters within string

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 2005 Forums

Transact-SQL (2005)

Finding characters within string

Author

Topic

VPeters
Starting Member

20 Posts

Posted - 2007-03-06 : 11:29:45

Hi all,

I am trying to separate a string field (OriginalField) into three fields.

OriginalField consists of numbers and/or dashes.

NewField1 must contain values from the left of the first dash (if there is one; else, value is OriginalField).

NewField2 must contain values from the right of the first dash or left of the second dash, depending on whether there are one or two dashes in OriginalField (else, value is 0).

NewField3 must contain values from the right of the second dash (if there is one; else, value is 0).

Does anyone know how to get the values between the 2nd and 3rd dashes, and after the 3rd dash??

Thanks!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-06 : 11:31:33

Hint: Use Substring, Charindex and CASE in conjunction.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 11:34:01

See this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80111
and this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80113

Peter Larsson
Helsingborg, Sweden

VPeters
Starting Member

20 Posts

Posted - 2007-03-06 : 11:34:43

I have been trying that, but I cannot seem to figure out how to handle the 2nd dash. I am able to evaluate how many dashes there are, and I can pull the data from the left of the first dash or ALL data to the right of the first dash, but I can't seem to figure out how to only pull data BETWEEN the two dashes, or AFTER the second dash.

VPeters
Starting Member

20 Posts

Posted - 2007-03-06 : 11:38:25

Oh yes, one important piece of information: the number of characters before/after the dashes can vary. There is no set position in which the dash may be.

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-06 : 13:38:05

ummmm..did you look at the links Peter Posted Prior

Say that 3 times fast

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

VPeters
Starting Member

20 Posts

Posted - 2007-03-06 : 13:43:55

Yes. But before I create a complicated function, I'd like to know how I'm supposed to actually USE it in my stored procedure. I have thousands of records to evaluate and separate out. Last time I tried to create a function and call it, I got an error telling me it returned more than one value. I am new to things like user-defined functions, and I do not know how to incorporate them properly into my code.

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-06 : 13:49:38

Do you have some sample data and what te expected result is suppose to be?

Post that....and what for example are you going to do with the data after you "separate it out"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 13:52:49

Rest assured that I am not nee to SQL and the function works.
In the two links I also provided code how to use them.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-06 : 14:10:38

quote:
Originally posted by VPeters

Yes. But before I create a complicated function, I'd like to know how I'm supposed to actually USE it in my stored procedure. I have thousands of records to evaluate and separate out. Last time I tried to create a function and call it, I got an error telling me it returned more than one value. I am new to things like user-defined functions, and I do not know how to incorporate them properly into my code.

Then you need to learn a valuable trick any programmer must master: how to create a small sample set of data, covering all of the cases you need to handle, and work on that. Then you can quickly run it, test it, tweak it and verify it before trying to apply it to your actual problem. Whenever you have some new technique to learn or some tricky code or formula to write, you should ALWAYS, in any language or technology you ever use, move off to the side and work in a small test area to hone your code and your skills.

Providing us with some sample data as well will greatly help us to help you.

- Jeff
http://weblogs.sqlteam.com/JeffS

VPeters
Starting Member

20 Posts

Posted - 2007-03-06 : 14:23:47

Yes, here is an example of several values in this field:

InventoryKey
0
10353-10360
1036
10361-10664-10669

Keep in mind, there is no actual consistency in the number of characters between the dashes.

What I would want my output to look like for the above values would be:

Opt1 Opt2 Opt3
0 0 0
10353 10360 0
1036 0 0
10361 10664 10669

Once the data is broken apart, the new values have to be used in a left join on the appropriate fields in another table. That is why I'm trying to keep this as simple as possible.

One thought I had was to evaluate the number of dashes in the field. If there are two dashes, I was going to try to replace the FIRST dash with a space. Then I would be able to pull only the characters to the right of the only dash that is left, and I would also be able to pull the characters between the space and the dash. However, I am unable to figure out how to do that. I was able to write the code to find the position number of the first dash, and then I used the SUBSTRING function to get the actual value (the dash) in that field, but as soon as I do a replace on it, I replace both dashes. I can't seem to figure out how to replace the value in that POSITION. Any ideas??

quote:
Originally posted by X002548

Do you have some sample data and what te expected result is suppose to be?

Post that....and what for example are you going to do with the data after you "separate it out"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 15:15:52

Following the advice given earlier, and following the instructions in the two provided links, gives this solution

-- prepare sample data
declare @t table (inventorykey varchar(200))

insert	@t
select	'0' union all
select	'10353-10360' union all
select	'1036' union all
select	'10361-10664-10669'

-- show the expected output
select	inventorykey,
	dbo.fnParseString(-1, '-', inventorykey) AS Opt1,
	ISNULL(dbo.fnParseString(-2, '-', inventorykey), 0) AS Opt2,
	ISNULL(dbo.fnParseString(-3, '-', inventorykey), 0) AS Opt3
from	@t

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-06 : 15:17:56

Here's a clue:

your data is not uniform, a you mentioned, so what can you do to make it easier to work with?

what if you appended '-0-0-' to each value as you select it from your table? so you get:

InventoryKey
0-0-0-
10353-10360-0-0-
1036-0-0-
10361-10664-10669-0-0-

Now, your data is uniform, and all values have at least 3 dashes. Your task now becomes:

1) find the first -. everything to the left is opt #1.
2) find the second '-'. everything between the first and second is opt #2
3) find the third '-'. everything between the second and third is opt #3

Some ideas for you to work with. the key is to simplify as much as you can and to "scrub" your input as best you can to make it fit your algorithm. What's nice about this is you don't need to worry about missing '-' characters because you have ensured that there will always be 3 of them.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 15:18:40

Oh, if you wonder, this is the output for the solution given above

inventorykey		Opt1	Opt2	Opt3
----------------------	------	------	------
0			0	0	0
10353-10360		10353	10360	0
1036			1036	0	0
10361-10664-10669	10361	10664	10669

Peter Larsson
Helsingborg, Sweden

VPeters
Starting Member

20 Posts

Posted - 2007-03-06 : 15:33:41

Peter,

Thank you so much for your help. I was working with another data analyst here and we created the function. With this last email and her help, I was able to understand how to use the function within our own code. This is great - very simple to insert into our existing code. And I have a feeling we will get a lot of use out of your function.

Thanks again.

quote:
Originally posted by Peso

Following the advice given earlier, and following the instructions in the two provided links, gives this solution
-- prepare sample data
declare @t table (inventorykey varchar(200))

insert @t
select '0' union all
select '10353-10360' union all
select '1036' union all
select '10361-10664-10669'

-- show the expected output
select inventorykey,
dbo.fnParseString(-1, '-', inventorykey) AS Opt1,
ISNULL(dbo.fnParseString(-2, '-', inventorykey), 0) AS Opt2,
ISNULL(dbo.fnParseString(-3, '-', inventorykey), 0) AS Opt3
from @t

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 16:21:59

You're welcome.

Please note that you can do both "left-to-right" and "right-to-left" numbering!
A negative number travels left to right, and a positive number travels right to left (to be compatable with PARSENAME function).

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources