Select statement - breaking out 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)

Select statement - breaking out string

Author

Topic

flipfyre
Starting Member

26 Posts

Posted - 2009-10-21 : 11:23:50

I want to write a SELECT statement that will break 2 parts of a string out, where there is a dash (-).

Example:

Entire Line:
11-532-000-102
1/2/3/4/5-11-906
0-1-504-99

So, I need to break each line into 2 parts, for 2 select statements. For the first select statement, I need all characters before the 1st dash:

Column:
11-532-000-102
1/2/3/4/5-11-906
0-1-504-99

Broke down like so:

1st part:
11
1/2/3/4/5
0

For the 2nd select statement, I need all characters after the first dash, but before the 2nd dash:

Column:
11-532-000-102
1/2/3/4/5-11-906
0-1-504-99

Broke out like so:

2nd part:
532
11
1

I was thinking of using a combination of the string function, CHARINDEX, like so:

CHARINDEX('-', Column) AS Position1

This gives me what position the first dash is at, but not sure what else to use it with.

Thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 11:46:52

use ParseValues function found in below link

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115544

and just use like


SELECT f.Val
FROM YourTable t
CROSS APPLY dbo.ParseValues(t.col,'-')f
WHERE f.ID = @Index

@Index is number of word you want separated by - (try 1,2...)

flipfyre
Starting Member

26 Posts

Posted - 2009-10-22 : 10:50:23

Thanks visakh16, works great. Question though. I have those newly separated values in a drop down box for users to select. How do I code my WHERE clause in the select statement to return everything from table where [ParseValues Code] = form.dropdownSelection?

IE:

SELECT *
FROM [TABLE]
WHERE
(SELECT DISTINCT f.val
FROM [TABLE] AS t CROSS APPLY dbo.ParseValues(t.Column, '-') AS f
WHERE (f.ID = 2)) = [user selection from form]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 11:33:31

where do you have your select statement, in line or broken out in a stored procedure. if in line I would try "SELECT ..... WHERE (f.ID = 2)) = " + [user selection from form]. I assume it is inline.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

flipfyre
Starting Member

26 Posts

Posted - 2009-10-22 : 11:56:30

Yosiasz,

It is inline, but I'm confused as to what you're suggesting. It looks like you're saying exactly what I typed above, but I would like further clarification.

If I run the above code in SQL Server with a manual entry (as if user selected item from code), I still get an error.

IE:

SELECT *
FROM [TABLE]
WHERE
(SELECT DISTINCT f.val
FROM [TABLE] AS t CROSS APPLY dbo.ParseValues(t.Column, '-') AS f
WHERE (f.ID = 2)) = 11

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 12:24:49

oops sorry boss! can't have WHERE (f.ID = 2)) = 11
it has to be WHERE f.ID = " + [user selection from form].
what is that funky WHERE (f.ID = 2)) where did that come from?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

flipfyre
Starting Member

26 Posts

Posted - 2009-10-22 : 12:34:40

The (f.ID = 2) comes from the initial question, answered by visakh16. Essentially, the query is breaking up code where the dashes are into sections.

IE:
11-532-000-102

Gets broken down into 11 (position 1) 532 (position 2) 000 (position 3) and 102 (position 4). By putting in f.ID = 2, I am saying return everything after the first dash, but before the second dash.

I'm now just trying to figure out how to put this in my where clause, so I can say, return everything from database where [column] = 11 (which would be the user selection from drop down box).

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 12:43:15

did you try WHERE f.ID = " + [user selection from form].

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

flipfyre
Starting Member

26 Posts

Posted - 2009-10-22 : 12:48:31

Let me see if I understand what you're trying to say.

SELECT *
FROM [TABLE]
WHERE
(SELECT DISTINCT f.val
FROM [TABLE] AS t CROSS APPLY dbo.ParseValues(t.Column, '-') AS f
WHERE (f.ID = ")) + 11(which would be the user selection)

If this is not what you're saying, will you please type the entire code out, as I am confused. I have tried different variations of what I believed you are saying, but doesn't work.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 13:02:20

yes it does not work because I posted wrong code..sorry, ok now that I have had some coffe here we go

SELECT *
FROM [TABLE]
WHERE
(
SELECT DISTINCT f.val
FROM [TABLE] AS t
CROSS APPLY dbo.ParseValues(t.Column, '-') AS f
WHERE f.ID = 11 --User Selection from form
)

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-22 : 13:10:18

quote:
Originally posted by flipfyre

Let me see if I understand what you're trying to say.

SELECT *
FROM [TABLE]
WHERE
(SELECT DISTINCT f.val
FROM [TABLE] AS t CROSS APPLY dbo.ParseValues(t.Column, '-') AS f
WHERE (f.ID = ")) + 11(which would be the user selection)

If this is not what you're saying, will you please type the entire code out, as I am confused. I have tried different variations of what I believed you are saying, but doesn't work.

are you using inline query or making this into a proc and calling from your application?

flipfyre
Starting Member

26 Posts

Posted - 2009-10-22 : 13:40:56

Inline query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-22 : 13:44:11

can i see your application code?

flipfyre
Starting Member

26 Posts

Posted - 2009-10-22 : 13:45:13

yosiasz, I tried your example, doesn't work.

flipfyre
Starting Member

26 Posts

Posted - 2009-10-22 : 13:49:12

Are you referring to SQL Server code, or web code?

SQL:

SELECT *
FROM [TABLE]
WHERE
(SELECT DISTINCT f.val
FROM [TABLE] AS t CROSS APPLY dbo.ParseValues(t.Column, '-') AS f
WHERE (f.ID = 2)) = 11

What it should return is all instances in the column where 2nd position = 11, like 1/2/3/4/5-11-906

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 13:49:51

"SELECT *
FROM [TABLE]
WHERE
(
SELECT DISTINCT f.val
FROM [TABLE] AS t
CROSS APPLY dbo.ParseValues(t.Column, '-') AS f
WHERE f.ID = " + [User Selection from form] + " --might need to convert calue to string if int
)"

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

flipfyre
Starting Member

26 Posts

Posted - 2009-10-22 : 13:59:33

yosiasz, that doesn't work either. I'm pretty sure I'll need to keep the (f.ID = 2) in there; otherwise, the query would search the whole column, instead of just the position I want to search.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-22 : 14:02:35

i meant application code which uses this query

flipfyre
Starting Member

26 Posts

Posted - 2009-10-22 : 14:21:55

visakh16, I can post the app code in here if you want, but my point is, it should work in sql alone, with a sample number, such as 11. If it doesn't work there, then it definitely isn't going to work in my app code. My app is Coldfusion, and all I'm doing is throwing a wrapper around the sql code like so:

<cfquery name="qTest" datasource="#database#" username="#dbid#" password="#dbpass#">
SELECT *
FROM [TABLE]
WHERE
1=1
<cfif IsDefined("form.DD") and form.DD neq "">
AND (SELECT DISTINCT f.val
FROM [TABLE] AS t CROSS APPLY dbo.ParseValues(t.Column, '-') AS f
WHERE (f.ID = 2) = #form.DD#)
</cfif>
</cfquery>

flipfyre
Starting Member

26 Posts

Posted - 2009-10-22 : 17:02:51

Nevermind guys. I just threw this code into a View and am able to query it fine. Thanks for all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources