April 17, 2008 at 5:52 am
Dear All,
I want to search a particular patterns from a given string.
Ex:
'Select CompanyName,CompanyId,Dept,Manager from Company A,Department B,Manager C where A.CompId = B.CompId
and B.MId = C.Mid'
From the above string i want to know how many tables are there after FROM clause.
create table #test
(
Str varchar(4000)
)
insert into #test
select "Select CompanyName,CompanyId,Dept,Manager from Company A,Department B,Manager C where A.CompId = B.CompId
and B.MId = C.Mid"
I have tried the below query:
select substring(Str,charindex('from',Str),charindex('where',Str)-charindex('from',Str) )
from #test
I got the below output:
from Company A,Department B,Manager C
Now, i want to split the above output like
Company A
Department B
Manager C
Going further, i want to split the above output to
TableName AliasName
Company A
Department B
Manager C
Thanks in advance!
karthik
April 17, 2008 at 6:24 am
[font="Verdana"]
from Company A,Department B,Manager C
Set @Temp = 'from Company A,Department B,Manager C'
Select @Temp = substring(@Temp, 6, Len(Temp)-5)
One step added so that finally you will have:
Company A,Department B,Manager C
OK, now have the above string with you. Further Create a temporaru table and then write a loop where you have to read character by character from the above string. Simultaneously inside the loop concatenate the characters until you get the blank space and when you will find space inbetween the string insert the concatenated string into a temporary table. and at the end of the loop write Select count(column of temporary table) from {temporary table}, so that you will the count of Tables in your from clause.
This is the rough idea I have tried to mentioned. Work on this and let is know.
By the way, come on Kartikeyan, I am eagarly waiting for Query Help - 4 😀 😀 😀 ... just kidding.
Mahesh
[/font]
MH-09-AM-8694
April 17, 2008 at 6:37 am
Set @Temp = 'from Company A,Department B,Manager C'
Select @Temp = substring(@Temp, 6, Len(Temp)-5)
One step added so that finally you will have:
Your query will fail,if i have one more space in between the FROM & Company A.
Do u agree ?
So , I refined my above query to
select ','+substring(substring(Str,charindex('from',Str),charindex('where',Str) - charindex('from',Str)),charindex(' ',substring(Str,charindex('from',Str),charindex('where',Str) - charindex('from',Str))),
len(substring(Str,charindex('from',Str),charindex('where',Str) - charindex('from',Str))))
from #test
Now, i got the below output.
,Company A,Department B,Manager C
Mahesh,
I know that Mr.Jeff Moden has given some solution to split the above kind of string. Just i am trying to implement it. Because he didn't use Cursor or loop concept.
Anyway, Thanks for your interest.I think you are waiting for my Query -4 release.Don't worry i will release it very soon.:)
karthik
April 17, 2008 at 6:49 am
http://www.sqlservercentral.com/Forums/Topic432183-8-1.aspx
i have tried to implement it. But i failed to implement.
Anybody help me out to resolve this issue.
karthik
April 18, 2008 at 3:20 am
Any Inputs ?
karthik
April 18, 2008 at 6:04 am
Any inputs will be highly appreciated.
karthik
April 18, 2008 at 6:44 am
Is this any help?
--SQL 2005 version
declare @s-2 varchar(1000)
set @s-2 = 'Company A,Department B,Manager C'
; with
integers1to100(i) as (select 1 union all select i+1 from integers1to100 where i < 100),
t as (select substring(@s, i, charindex(',', substring(@s, i, 100)+ ',')-1) as x
from integers1to100 where substring(',' + @s-2, i, 1) = ',' and i <= len(@s))
select cast(substring(x, 0, charindex(' ', x)) as varchar(30)) as TableName,
cast(substring(x, charindex(' ', x) + 1, 100) as varchar(10)) as AliasName
from t
--SQL 2000 version
declare @s-2 varchar(1000)
set @s-2 = 'Company A,Department B,Manager C'
declare @integers1to100 table(i int)
insert @integers1to100 select distinct number from master..spt_values where number between 1 and 100
select cast(substring(x, 0, charindex(' ', x)) as varchar(30)) as TableName,
cast(substring(x, charindex(' ', x) + 1, 100) as varchar(10)) as AliasName
from (
select substring(@s, i, charindex(',', substring(@s, i, 100)+ ',')-1) as x
from @integers1to100 where substring(',' + @s-2, i, 1) = ',' and i <= len(@s)) a
/* results
TableName AliasName
------------------------------ ----------
Company A
Department B
Manager C
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 21, 2008 at 1:59 am
Ryan,
Thanks.But you have hardcoded it as a seperate string. But i don't want to hard code it.
Because it may change in future like From Company A,Department B,Employee C,EmpHistory D
In that case we need to change our string part. query will have to do it dynamically.
karthik
April 21, 2008 at 2:50 am
You've lost me. Give a few examples of inputs and required outputs...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 21, 2008 at 3:10 am
Ok.
FOR EXAMPLE,
Create table ColumnDesc
(
ID int,
ColumnList varchar(4000)
)
Insert into ColumnDesc
Select 1,'Select A.Eno,B.Salary,C.Dept from Emp A,Payroll B,Department C'
Union All
Select 2,'Select A.Eno,A.Ename,B.Dob from Emp A,Payroll B'
Union All
Select 3,'select A.Eno,A.Ename,A.Address,A.Email,B.Salary,B.DOB,C.Dept,D.Remarks from Emp A,Payroll B,Department C,EmpHistory D'
Select * from ColumnDesc
Output:
ID ColumnList
1 Select A.Eno,B.Salary,C.Dept from Emp A,Payroll B,Department C
2 Select A.Eno,A.Ename,B.Dob from Emp A,Payroll B
3 select A.Eno,A.Ename,A.Address,A.Email,B.Salary,B.DOB,C.Dept,D.Remarks from Emp A,Payroll
B,Department C,EmpHistory D
Now i want to split the above one as
.......................................
ID TableName AliasName
.......................................
1 Emp A
1 Payroll B
1 Department C
2 Emp A
2 Payroll A
3 Emp A
3 Payroll B
3 Department C
3 EmpHistory D
.........................................
I hope, I make you clear now. If not pls let me know.
karthik
April 21, 2008 at 3:52 am
This is a lot easier in SQL 2005 since you can use 'cross apply'. In SQL 2000, I think you will have to loop through your rows using a while loop or a cursor and process the data one row at a time into another table.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 21, 2008 at 4:00 am
I am using sql2000.
But i dont want to use while loop or Cursor. I want to acheive it by using SET BASED logic.
Can anybody help me ?
karthik
April 21, 2008 at 4:19 am
Since you insist...
declare @ColumnDesc table (ID int, ColumnList varchar(4000))
Insert into @ColumnDesc
Select 1,'Select A.Eno,B.Salary,C.Dept from Emp A,Payroll B,Department C'
Union All Select 2,'Select A.Eno,A.Ename,B.Dob from Emp A,Payroll B'
Union All Select 3,'select A.Eno,A.Ename,A.Address,A.Email,B.Salary,B.DOB,C.Dept,D.Remarks from Emp A,Payroll B,Department C,EmpHistory D'
declare @integers1to1000 table(i int)
insert @integers1to1000 select distinct number from master..spt_values where number between 1 and 1000
select ID, left(substring(x, i, 4000), charindex(',', substring(x, i, 4000) + ',') - 1) as TableAndAlias
from (Select ID, substring(ColumnList, patindex('% from %', ColumnList) + 6, 4000) as x from @ColumnDesc) a
inner join @integers1to1000 b on substring(',' + x, i, 1) = ','
order by ID
/*
ID TableAndAlias
----------- -------------------
1 Emp A
1 Payroll B
1 Department C
2 Payroll B
2 Emp A
3 Emp A
3 Payroll B
3 Department C
3 EmpHistory D
*/
You can combine this with the other code I gave you and you're away.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 21, 2008 at 6:24 am
Thanks Ryan.
Again i want to add some more logics.
Suppose if any user want to add a new column in the select list,our query has to do it.
Say for example,
ID ColumnList
1 Select A.Eno,B.Salary,C.Dept from Emp A,Payroll B,Department C
2 Select A.Eno,A.Ename,B.Dob from Emp A,Payroll B
3 select A.Eno,A.Ename,A.Address,A.Email,B.Salary,B.DOB,C.Dept,D.Remarks from Emp A,Payroll
B,Department C,EmpHistory D
Let us assume, An user has choosed ID 1 and they want to introduce a new column in the select list.
i.e Expected ColumnList is
1 Select A.Eno,A.EAge,B.Salary,C.Dept,C.DeptName from Emp A,Payroll B,Department C
So far they did it manually.But now it has to be added dynamically.
Thing is, we dont know that particular column is available in which table.
Say for example, They blindly introduce EAge. We have to identify the column which is available in which table name ?
I think we can write query like this
Create procedure P1
(
@NewColumnName varchar(100)
)
as
begin
Create table #TBLName
(
TableName varchar(100)
)
Insert into #TBLName
Select object_name(id) from syscolumns
where type = 'U'
and name = @NewColumnName
/* Assume the following output is stored in a seperate table called Final
ID TableAndAlias
----------- -------------------
1 Emp A
1 Payroll B
1 Department C
2 Payroll B
2 Emp A
3 Emp A
3 Payroll B
3 Department C
3 EmpHistory D
/*
Declare @AliasName char(1)
if exists ( select 1 from #TBLName where TableName in ( select substring(TableAndAlias,1,charindex(' ',TableAndAlias)) from Final where Id = 1))
Begin
select @AliasName = substring(TableAndAlias,charindex(' ',TableAndAlias),1)
from #TBLName where TableName in ( select substring(TableAndAlias,1,charindex(' ',TableAndAlias)) from Final where Id = 1)
Update ColumnDesc
set ColumnList = substring(ColumnList,1,patindex('%From%')+6) + @AliasName+'.'+#TBLName.TableName +
substring(ColumnList,patindex('%From%'))+6,substring(ColumnList,patindex('%From%')+6) - Len(ColumnList)
End
End -- End of Procedure
Experts,
Please give me your valuable suggestions.
karthik
April 21, 2008 at 7:36 am
I think this will work, if I understand what you're doing correctly - using Ryan's example, modify the assignment of @s-2 to be something like this:
DECLARE @maxrows int, @row int
SET @maxrows = (SELECT max(ID) from ColumnDesc)
SET @row = 0
WHILE @row<@maxrows
BEGIN
SET @s-2 = 'SELECT ColumnList from ColumnDesc WHERE ID = '+@row
EXEC @s-2
-- then continue with Ryan's example. After you're done splitting the string, increment @row and loop again for the next ColumnList
SET @row = @row + 1
END
That should give you each ColumnList regardless of the size of the ColumnDesc table.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply