Trying to return strings starting with 'dbo.'

Question

Post reply

Trying to return strings starting with 'dbo.'

JaybeeSQL

Hall of Fame

Points: 3501
More actions
January 5, 2022 at 4:14 pm
Go to Answer
#3970190

Hi all,
As the title implies I want the characters to the right of "dbo.", which - you guessed it - are tablenames, but of course I need to experiment with the length as some will be 5 characters long, others 50.
Of course I had
Select Tablename
From ListOfTables
Where Tablename like'%dbo.%'
But this doesn't come close.
TIA

Mike01

SSChampion

Points: 11395
More actions
January 5, 2022 at 5:00 pm
Answer
#3970223
```
declare @t table (tablename varchar(100))

insert into @t values 
('dbo.mytable'),
('etl.dbo.table'),
('koagvakjnonsenseajg4648.dbo.RandomTableForSSChahfhaijfhfhf5664646'),
('nonsensetable')

select tablename,
Substring(tablename, charIndex('dbo.',tablename), len(tablename))
from @t
where charIndex('dbo.',tablename) > 0
```
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

You are matching things that might be

- dbo.mytable

- etl.dbo.table

I'd use

select TableName
 from ListofTables
 where Left(TableName, 4) = 'dbo.'

Repro:

WITH ListofTables (TableName)
AS (   SELECT         s.name + '.' + o.name AS TableName
       FROM
                      sys.objects o
           INNER JOIN sys.schemas AS s
               ON o.schema_id = s.schema_id
       WHERE          type = 'U')
SELECT TableName FROM ListofTables WHERE LEFT(TableName, 4) = 'dbo.';

ScottPletcher SSC Guru Points: 101120 More actions · Answer 2

If you just want table names that start with 'dbo.', then you can do this:

SELECT STUFF(Tablename, 1, 4, '') AS Tablename
FROM dbo.ListOfTables
WHERE Tablename LIKE 'dbo.%' /*allows an index seek on Tablename, if available*/

Are there multiple tables names in one row? Is that why you included a '%' in the front of the string?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 3

Neither set of code is coming up with a meaningful result set, not sure where the disconnect here is, but I'll try again:

If a row contains:

koagvakjnonsenseajg4648.dbo.RandomTableForSSChahfhaijfhfhf5664646

I want to return the dbo. string PLUS any x characters to the right of said string, so I need to be able to specify the number of characters after dbo. that are returned. In the above case the 'RandomTableForSSC' tablename has 17 characters, I'd start with 17 and try some larger numbers, I don't mind as the data doesn't have to be cleansed.

This reply was modified 2 years, 11 months ago by JaybeeSQL.

ScottPletcher SSC Guru Points: 101120 More actions · Answer 4

SELECT SUBSTRING(TableName, CHARINDEX('dbo.', TableName), 8000) AS TableName

FROM dbo.whatever

WHERE TableName LIKE '%dbo.%'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 5

I marked Mike's as the script that satisfied my requirement but to be fair Scott's came closest - I can't however changed the marked answer.

Thank you gentlemen 🙂

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

JaybeeSQL wrote:

Neither set of code is coming up with a meaningful result set, not sure where the disconnect here is, but I'll try again:
If a row contains: koagvakjnonsenseajg4648.dbo.RandomTableForSSChahfhaijfhfhf5664646
I want to return the dbo. string PLUS any x characters to the right of said string, so I need to be able to specify the number of characters after dbo. that are returned. In the above case the 'RandomTableForSSC' tablename has 17 characters, I'd start with 17 and try some larger numbers, I don't mind as the data doesn't have to be cleansed.

Does the table name exist in a database anywhere that's accessible??

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)