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!

How to separate the substrings from the string 3

Status
Not open for further replies.
techipa

techipa

Programmer
Feb 12, 2007
85
US
Hi All,

Can anyone tell me how to separate the string into substrings and store the values in variables

I have a substring called
CPPS.PRIMARY.P01.BAPI RM TEMP

I need the output as follows

@var1 = PRIMARY
@var2 = P01
@var3 = BAPI RM TEMP

Thanks in advance

-techiPA

 
Sort by date Sort by votes
Since your string seems to be delimited by "."s, then I would use a split function for this.
Read this FAQ:
faq183-5207
 
Upvote 0 Downvote
IF there will only be 4 pieces of data seperated by the dot, then you can use the ParseName function.

Ex:

Code: 
Declare @Temp VarChar(100)
Set @Temp = 'CPPS.PRIMARY.P01.BAPI RM TEMP'

Select ParseName(@Temp, 1),
       ParseName(@Temp, 2),
       ParseName(@Temp, 3),
       ParseName(@Temp, 4)


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
OMG, George. How do you know all these functions! I was trying to write this using charindex, len and substring functions. And you do it in a couple lines of code!

Amazing!

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

Thanks for the reply.

The data may not be in uniform fasion. I am usign CURSOR to get the column data

Sometimes the data might be

CPPS.PRIMARY.P01.BAPI RM TEMP
CPPS.RPM.RM021P1
CPPS.PRIMARY.P05.LH.RMTEMP
CPPS.PRIMARY.STAGING021:ROOM TEMP
CPPS.SECONDARY.LABELCTRL.052:ROOM TEMP

May I knwo in that case how would I separate the data

Thanks,
-techiPA
 
Upvote 0 Downvote
Since you may occasionally have more than 4 parts to the data, you will NOT be able to use ParseName.
Ex: CPPS.PRIMARY.P05.LH.RMTEMP

In that case, you could write your own function, or do as jbenson001 suggests and use a split function.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
>> How do you know all these functions!

This has come up before, where parsename was used. It's an old trick. SQL Server uses this function within it's system stored procedures to seperate the 4 part naming convention server.database.owner.table, so, if you get in to the habit of looking at the source for the system stored procedures, you'll see it a lot.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
I guess I need to start using sp_helptext a lot more to look at system procedures. So ParseName will only work with the 4 part naming convention?


- Paul
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
>> So ParseName will only work with the 4 part naming convention?

Yes.

You could create a similar function that can accomodate more. Like this...

Code: 
Create Function SplitPosition
  (
  @CommaDelimitedFieldNames Varchar(8000),
  @SplitChar VarChar(10), 
  @Position Int
  )  
Returns varchar(8000)
As
Begin 
  Declare @Tbl_FieldNames Table (Position Int Identity(1,1), FieldName VarChar(8000))
  Set @CommaDelimitedFieldNames =  @CommaDelimitedFieldNames + @SplitChar

  Declare @Pos1 Int
  Declare @pos2 Int
 
  Set @Pos1=1
  Set @Pos2=1

  While @Pos1<Len(@CommaDelimitedFieldNames)
    Begin
      Set @Pos1 = CharIndex(@SplitChar,@CommaDelimitedFieldNames,@Pos1)
      Insert @Tbl_FieldNames Select  Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(8000))
      Set @Pos2=@Pos1+1
      Set @Pos1 = @Pos1+1
    End 
 Return (Select FieldName From @tbl_FieldNames Where Position = @Position)
End

You could then use it like this...

Code: 
Declare @Temp VarChar(100)
Set @Temp = 'CPPS.PRIMARY.P05.LH.RMTEMP'

[green]-- Notice how ParseName fails[/green]
Select ParseName(@Temp, 1),
       ParseName(@Temp, 2),
       ParseName(@Temp, 3),
       ParseName(@Temp, 4)

Select dbo.SplitPosition(@Temp, '.', 1),
       dbo.SplitPosition(@Temp, '.', 2),
       dbo.SplitPosition(@Temp, '.', 3),
       dbo.SplitPosition(@Temp, '.', 4),
       dbo.SplitPosition(@Temp, '.', 5)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
techipa,

I've given you a function you can use to seperate the data. Now, I'd like to encourage you to NOT use it. Obviously, each piece of data that is seperated by the dot represents something different. You really should re-design your table so that each piece of data is stored in a seperate field. By doing this, the queries will be simpler to write. This process (of seperating the data in to multiple column) is called 'database normalization'. I strongly encourage you to do some reading about this topic.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Thanks a lot for your prompt reply George.

You are really amazing. I need to understand this function you wrote.

Let me go through it first.

Thanks again
-techiPA
 
Upvote 0 Downvote
nice function George.

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

The data is coming from CSV file. Using DTS I am importing it in SQL table. From there I have to massage it and separate it so that the users can perform the query.
There is no standard format of this data in csv file.
That is where I am having issues.

If you see my yesterday's post 'Help in creating the DTS package' you will know what I wanted to achieve.

Thanks again
-techiPA
 
Upvote 0 Downvote
How far have you gotten in the process?

I would follow SQL Sister's advice of importing this data in to a table with 2 columns. 1 column would be an identity column, and the second would be a large varchar column. Then, I would pick apart the information in to their seperate fields.

Have you got this data in a table yet? Can you show some sample data from that table?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
George,

I created the table in SQL with 4 columns and got the data in it

Here is a sample (C1 - Identity col, C2 - Date, C3-Roomtype,Reading type, C4 - Reading)

C1 C2 C3 C4
1 Point System Name: CPPS.PRIMARY.P01.BAPI RM TEMP NULL
2 Trend Every: 15 Min NULL
3 Date Range: 10/31/2006 - 10/31/2006 NULL
4 Report Timings: All Hours NULL
5 10/31/2006 00:00:00 70.71
6 10/31/2006 00:15:00 70.71
7 10/31/2006 00:30:00 69.70

These are just few records for 'TEMPERATURE' type at room P01.

Please advice me if you think of better way to implement this so far

Thanks
-techiPA
 
Upvote 0 Downvote
As you're using DTS you could split the data into multiple columns using the built in VBScript parser which has a Split function that is generally more powerful than anything in SQL Server's string handling functions as it can create arrays of strings.

Start by creating an empty table that you want the data to end up in with all the columns defined. In the example below I've assume that they're called SubFld1, SubFld 2 etc . and that the Temperature data goes into a column called TempCol.

Using the DTS Wizard select the csv file as the source and select the new table as the destination.

Click on the Transform button and make sure that you select Delete Rows in Destination table rather than Create Destination table.

Click on the Transformations tab and select Transform Data as it is copied.

Edit the code to look something like this:
Code: 
'**********************************************************************
'  Visual Basic Transformation Script
'  Copy each source column to the
'  destination column
'************************************************************************

Function Main()
    ' copy over any unchanged columns
	DTSDestination("Col001") = DTSSource("Col001")
	DTSDestination("Col002") = DTSSource("Col002")
	DTSDestination("Col003") = DTSSource("Col003")

    Dim Words ' this will become a zero based array of strings
    Dim TempText, i

    ' this next bit might not work perfectly looking at the data you have supplied
    ' and you might need to replace other stray delimiters in the same way
    TempText = Replace((DTSSource("TemperatureText"), ":", ".")

    ' split the required field using "." 
    Words = Split(TempText"), ".")
    Dim WSize
    WSize = UpperBound(Words)   ' the size of the array
	DTSDestination("SubFld1") = Words(1) ' the second part - always present
	DTSDestination("SubFld2") = Words(2) ' the third part - always present?
    For i = 3 to WSize
        If Instr(Words(i), "TEMP", 1) <> 0 Then
            DTSDestination("TempCol") = Words(i)
            DTSDestination("SubFld" & i) = ""
        Else
            DTSDestination("SubFld" & i) = Words(i)
        end if
    Next
	Main = DTSTransformStat_OK
End Function

If you use this technique you get the data straight into the format you require and don't have to figure out how to use SQL Server's relatively limited language and string functions to solve the problem.

Bob Boffin
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
371
Chris Miller
Chris Miller
dgillz
  • Locked
  • Question
How to convert varchar to time? 2
Replies
1
Views
432
Chris Miller
Chris Miller
bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
247
bharons
bharons
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
145
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
287
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top