Extracting data from a text value

[cpark87]

I am trying to compare columns in two tables to see if there is a match. I want to trim or remove part of the data in this
columns before looking for a match.

I have a table titled v_WIN_PRINTERS_T_TH and the column name is WPT_WPN_PORT. Data in this field is similiar to this:

\\ATR110F03\PQ_CLP1620_TEST

I need to trim or remove \\ATR110F03\ from this column so PQ_CLP1620_TEST is left... Basically I need to remove data between \\Some value and \ and leave the rest

I then want to compare against a column in another table titled jetadmin, column name = IPX_NAME

Any sugestions??

 

[Nevermoor]

can't you just use a like comparison?

i.e. dbo.jetadmin.IPX_NAME like '%\PQ_CLP1620_TEST'

 

[cpark87]

I know what you are saying.. If I was going one by one it would work... I wanted to try and run a query and build a relationship between the two tables to come up with a match.

Here is an example...

v_WIN_PRINTERS_T_TH.WPT_WPN_PORT = \\ATR110F03\PQ_CLP1620_TEST

jetadmin.IPX_Name = PQ_CLP1620_TEST

I need to tie the two together through a join but the data doesn't match. So I won't to strip off everything before the \\SomeValue\.. This data I am going to strip off is different from record to record.

Let me know if that makes more sense. I didn't really know how to word this one.

 

[Nevermoor]

Do you mean something like this?

Select dbo.jetadmin.IPX_NAME, dbo.v_WIN_PRINTERS_T_TH.WPT_WPN_PORT
FROM dbo.jetadmin INNER JOIN
dbo.v_WIN_PRINTERS_T_TH
ON dbo.v_WIN_PRINTERS_T_TH.WPT_WPN_PORT
like '%\'+ dbo.jetadmin.IPX_NAME

 

[cpark87]

Thanks... That did work..

Say I wanted to trim off the \\SomeValue\ of that field and insert into a different table.

I have seen some references on using the LEFT or RIGHT commands but usually referring to the amound of characters.

 

[Nevermoor]

are you trying to do the trimming in SQL or on an ASP page?

(an asp page is easy, you can just do something like:

tempArray = split ("\\SomeValue\ANYTHING","\")

whatWeWant = tempArray(2)
'whatWeWant = SomeValue so long as there is no "\" in SomeValue

I don't know if there is a split function for SQL Server, however I bet you could find one someone has written if split() is the way you want to go.

 

[cpark87]

I would be running in SQL. Thanks for the feedback I will try doing some more research....