Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parsing the string 2

Status
Not open for further replies.
techipa

techipa

Programmer
Feb 12, 2007
85
US
Hello All,

There is a column in sql table which contains the data like:

RA-100 R1
RA-200.02.01 ORIGINAL
RA-200.02 R1
GSOP 2709 ORIG
GSOP 2703A ORIGINAL
SOP 112 R 13
SOP 117A R1

There is some uniformity in the data but not a lot. I need to parse the data in three columns as:
(First column - all chars from left till space (' ') or '-'
second column - all chars from space/'-' till first occurance of space
third column - the revision number)

Cateory Type Revision

RA 100 R1
RA 200.02.01 ORIGINAL
RA 200.02 R1
GSOP 2709 ORIG
GSOP 2703A ORIGINAL
SOP 112 R 13
SOP 117A R1

May I know how would I achieve this? Do I have to use Charindex function?

Thanks a lot,
-techiPA

 
Sort by date Sort by votes
Yes you will have to use charindex.
Here is the example for the on with the '-'

Code: 
DECLARE @myname varchar(400)
SELECT @myname = 'RA-100 R1'
--SELECT CHARINDEX('-',@myname)+1,CHARINDEX(' ',@myname,CHARINDEX('-',@myname)) -CHARINDEX('-',@myname)
SELECT SUBSTRING(@myname,1,CHARINDEX('-',@myname)-1)
      ,SUBSTRING(@myname,CHARINDEX('-',@myname)+1,CHARINDEX(' ',@myname,CHARINDEX('-',@myname)) -CHARINDEX('-',@myname))
      ,SUBSTRING(@myname,CHARINDEX(' ',@myname)+1,len(@myname))

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Thanks Paul,

The SQL server does not like sql finctions like substring, charindex etc.. because I am trying to get the data from MS Access server which I linked to sql server and am using openquery.
For example:

Select * from OpenQuery(DB2000, 'Select SUBSTRING([Col1],1,5) from table1')

This is new to me. I will have to find the similar function in access. Or the easiest way would be insert the col1 in sql table and then use sql syntax to parse the data.

Thanks
-techiPA
 
Upvote 0 Downvote
Substring in access = mid(Column, StartPosition, NumOfCharacters)
Charindex in access = instr(Column, SearchString)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Thanks Paul and Alex,

I will have to pull the data from Access to SQL and then parse it.

Paul, I used your sample and added one more OR condition for ' ' but I got error? How would I check for ' ' and '-' at a time?
 
Upvote 0 Downvote
I think I would use an IF\ELSE. Write one for '-' and one for ' '. George may know how to do this one select but I don't.
sorry.

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Thanks Paul for the reply.

George, would you know how to write single sql statement for ' ' and '-'?

Thanks in advace,
-techiPA
 
Upvote 0 Downvote
George can't come to the phone right now, can I take a message?

Anyway, here is a way to do it in one select, using PATINDEX instead of CHARINDEX. You can read in books online about the difference between the two:

Code: 
[COLOR=blue]DECLARE[/color] @myname [COLOR=blue]varchar[/color](400)
[COLOR=blue]SELECT[/color] @myname = [COLOR=red]'RA-100 R1'[/color]

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]LEFT[/color](@myname, [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[ -]%'[/color], @myname)-1)
, [COLOR=#FF00FF]SUBSTRING[/color](@myname
	, [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[ -]%'[/color], @myname) + 1
	, [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[ -]%'[/color], [COLOR=#FF00FF]substring[/color](@myname, [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[ -]%'[/color], @myname) + 1, len(@myname))))
,[COLOR=#FF00FF]right[/color](@myname, [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[ -]%'[/color], [COLOR=#FF00FF]reverse[/color](@myname)) - 1)

Hope it helps,

Alex



Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Sorry guys, but I'm swamped today. Try playing with the PatIndex function with a search string of '%[ -]%'.

Use this to get started.

Code: 
[COLOR=blue]DECLARE[/color] @myname [COLOR=blue]varchar[/color](400)
[COLOR=blue]SELECT[/color] @myname = [COLOR=red]'ABC-123'[/color]

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Left[/color](@myname, [COLOR=#FF00FF]PatIndex[/color]([COLOR=red]'%[ -]%'[/color], @myname)-1), [COLOR=#FF00FF]Right[/color](@myname, Len(@MyName) - [COLOR=#FF00FF]PatIndex[/color]([COLOR=red]'%[ -]%'[/color], @myname))

[COLOR=blue]SELECT[/color] @myname = [COLOR=red]'XYZ 987'[/color]

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Left[/color](@myname, [COLOR=#FF00FF]PatIndex[/color]([COLOR=red]'%[ -]%'[/color], @myname)-1), [COLOR=#FF00FF]Right[/color](@myname, Len(@MyName) - [COLOR=#FF00FF]PatIndex[/color]([COLOR=red]'%[ -]%'[/color], @myname))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
I couldn't account for spaces and '-' in one select so I wrote it this way. It does work.

Code: 
[COLOR=blue]DECLARE[/color] @myname [COLOR=blue]varchar[/color](400)
[COLOR=blue]SELECT[/color] @myname = [COLOR=red]'GSOP 2703A ORIGINAL'[/color]

[COLOR=blue]IF[/color] [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'-'[/color],@myname) > 0
[COLOR=blue]BEGIN[/color]
[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]SUBSTRING[/color](@myname,1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'-'[/color],@myname)-1)
      ,[COLOR=#FF00FF]SUBSTRING[/color](@myname,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'-'[/color],@myname)+1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],@myname,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'-'[/color],@myname)) -[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'-'[/color],@myname))
      ,[COLOR=#FF00FF]SUBSTRING[/color](@myname,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],@myname)+1,len(@myname))
[COLOR=blue]END[/color]
[COLOR=blue]ELSE[/color]
[COLOR=blue]IF[/color] [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],@myname)>0
[COLOR=blue]BEGIN[/color]
[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]SUBSTRING[/color](@myname,1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],@myname)-1)
      ,[COLOR=#FF00FF]SUBSTRING[/color](@myname,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],@myname)+1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],@myname,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],@myname)+1) -[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],@myname))
      ,[COLOR=#FF00FF]SUBSTRING[/color](@myname,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],@myname,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],@myname)+1) ,len(@myname))
[COLOR=blue]END[/color]

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Hey George (or Alex)
What is more efficiant, CHARINDEX or PATINDEX. Is there a reason to use one over the other?

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Thanks George,


- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
I said, "PatIndex gives you more functionality. "

This isn't strictly true. To be more specific, PatIndex offers similar (but different) functionality. Under most curcumstances, either can be used, but both functions have their own advantages depending on the task at hand.

PatIndex allows for pattern searching with wildcards.
CharIndex allows you to specify a starting location.

The challenges faced by techipa in this thread show the perfect example of where patindex comes in handy. Essentially, we want to search for a space OR a dash. This can't be done with a single call to charindex, but can be done with patindex.

If the task was to find the first spaces after the 5th character, then charindex would be a better solution.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
TechiPA,
Use Alex example if you can. I know it's Access so you might not be able to. If it won't work through the link table you may have to use my example.

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
techipa - I would use the query you posted

Code: 
Select * from OpenQuery(DB2000, 'Select SUBSTRING([Col1],1,5) from table1')

To populate a temp table/table variable, then do your manipulation in there, and then update your access table from this temporary area. It will probably be less messy.

Paul - to the best of my knowledge, Access does not like if statements in queries either. You would need to write a vba function to handle the parsing (or an EXTREMELY convoluted query), and I am not sure either of these would be accessible to SQL Server.


Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
I don't use OpenQuery very often, but wouldn't something like this work?

Code: 
[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]LEFT[/color]([Col1], [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[ -]%'[/color], [Col1])-1)
, [COLOR=#FF00FF]SUBSTRING[/color]([Col1]
    , [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[ -]%'[/color], [Col1]) + 1
    , [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[ -]%'[/color], [COLOR=#FF00FF]substring[/color]([Col1], [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[ -]%'[/color], [Col1]) + 1, len([Col1]))))
,[COLOR=#FF00FF]right[/color]([Col1], [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[ -]%'[/color], [COLOR=#FF00FF]reverse[/color]([Col1])) - 1)
[COLOR=blue]From[/color] OpenQuery(DB2000, [COLOR=red]'Select [Col1] from table1'[/color])

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Hopefully :)

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Thank you George, Paul and Alex. Friday was holiday so got delayed on reply.

Paul, I implemented your suggestioned SQL and it ran like charm. The query returned the expected results.

Thanks again SQL GURUs,
-techiPA
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
379
Chris Miller
Chris Miller
Auguy
  • Locked
  • Question
Connection String in .NET program Config File 2
Replies
10
Views
269
Auguy
Auguy
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
148
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question
How to display part number based on search string by chemical id ?
Replies
3
Views
120
gmmastros
gmmastros
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
227
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top