March 24, 2011 at 12:29 pm
How can I pull just "dbname.dbo.db_item_tbl" out of the column2 string?
column 1
BA_INVENTORY_LOCATION_PROC
column2
(select UPPER(en_item_sdesc) from dbname.dbo.db_item_tbl d_item_tbl where db_item_tbl.db_item_key=a.xx_item_key),
I am so not good with finding & pulling things from a string....
thx
Jude
March 24, 2011 at 12:33 pm
What you wrote isn't clear. Is that what is exactly stored in the column?
It's not clear what you are trying to do?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 24, 2011 at 1:01 pm
Yes, column2 is pulled from another table, but I only want the part of the string that contains dbname.dbo.xx_xxx_tbl no matter what position is exists in the string.
Hope this makes sense....
March 24, 2011 at 1:04 pm
Also, this is just a big text column & sometimes starts with an Inner Join dbname.dbo.xx_xxx_tbl & I don't want any other characters before or after, I just want to strip out "dbname.dbo.xx_xxx_tbl" exactly.
March 24, 2011 at 1:07 pm
Have you tried charindex()?
Like:
select
charindex( col2, 'dbname.dbo.'),
substring( col2, charindex( col2, 'dbname.dbo'), 30)
from myTable
It would help if you explained what you are trying to do and what the table looks like? Are you trying to figure out what tables are in this string? If so, you might have multiple tables. What do you do there?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 24, 2011 at 1:17 pm
Actually this suggestion;
"select
charindex( col2, 'dbname.dbo.'),
substring( col2, charindex( col2, 'dbname.dbo'), 30)
from myTable
"
is Great!!!
thx so much!
March 24, 2011 at 2:27 pm
Actually, you can simplify it if you're looking for table names in the local database.
You can do a join using "LIKE" to sys.tables.
select *
from MyTable
inner join sys.tables as Tbls
on MyTable.Col2 LIKE '%' + Tbls.name + '%'
That's often easier than substring pattern matching, if it's applicable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 25, 2011 at 7:56 am
Unfortunately it's not in the same database, I am retreiving cross database references & it's actually going pretty good with you help.
Can I bother you one more time (I hope)
Pulling apart the string leaves things on the end that I don't want, I have substringed the database name, schema name and table name (l have my data in another table from using a while loop & sp_helptext )
I need to strip everything off my shortened column after the characters 'tbl'.
For instance; adbname.dbo.xx_xxxx_tbl a, i want to retrieve out the xx_xxxx_tbl into a new column.
And I would need to have another column for the names that are a bit longer such as xx_xxxxxxxxx_tbl.
Ideas ?
thx!!!!
March 25, 2011 at 8:57 am
What you want to do is use charindex to find the other pattern. This is all pattern matching, so you can do this:
select
charindex( col2, 'dbname.dbo.'),
charindex( col2, '_tbl', charindex( col2, 'dbname.dbo.')),
substring( col2, charindex( col2, 'dbname.dbo'), charindex( col2, '_tbl', charindex( col2, 'dbname.dbo.')))
from myTable
If you don't understand, post back, but try to figure it out.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply