May 26, 2009 at 9:09 pm
Hi Friends
I have a string like this
declare @Text NVARCHAR(max)
set @Text='
DECLARE @userid INT
SELECT * FROM [User] WHERE UserId=@userId
DECLARE @Id INT,@IS as NVARCHAR(10)
SELECT CAST(1 BIGINT)
SELECT * FROM [Role]
SELECT CAST(@id NVARCHAR(100))
declare @IG nvarchar(100)
select * from Progressdata where UserId= cast(@UserId NVARCHAR(100))
select cast(@Id decimal(5,2))
'
I need to Stuff the word 'AS' in between Cast functions parameter and DataType
in my string it is like cast(@Id decimal(5,2))
i need it as cast(@Id as decimal(5,2))
and it should be effective for all Cast Function also in that String
at last i need My string as
'
DECLARE @userid INT
SELECT * FROM [User] WHERE UserId=@userId
DECLARE @Id INT,@IS as NVARCHAR(10)
SELECT CAST(1 as BIGINT)
SELECT * FROM [Role]
SELECT CAST(@id as NVARCHAR(100))
declare @IG nvarchar(100)
select * from Progressdata where UserId= cast(@UserId AS NVARCHAR(100))
select cast(@Id as decimal(5,2))
'
Plz do the needFul thing
Thanx
With Regards
Ningaraju
May 27, 2009 at 8:51 am
Declare @Text NVARCHAR(max)
Set @Text='
DECLARE @userid INT
SELECT * FROM [User] WHERE UserId=@userId
DECLARE @Id INT,@IS as NVARCHAR(10)
SELECT CAST(1 BIGINT)
SELECT * FROM [Role]
SELECT CAST(@id NVARCHAR(100))
declare @IG nvarchar(100)
select * from Progressdata where UserId= cast(@UserId NVARCHAR(100))
select cast(@Id decimal(5,2))
'
set @Text = REPLACE(@text, 'cast(1' , 'cast(1 as ')
set @Text = REPLACE(@text, 'cast(@id', 'cast(@id as ')
set @Text = REPLACE(@text, 'cast(@userid', 'cast(@userid as ')
print @text
The above is a quick and dirty way of doing it. I am not sure what you are trying to accomplish though. If it is just this code above that needs to be fixed, it's easy since we know which all occurrences of CAST we need to modify.
I did try to find a generic way of solving this; I wanted to avoid hard coding the cast expressions that need to be altered. But I could not find a satisfactory way.
Let me know if this solves your problem.
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 27, 2009 at 9:21 pm
Thanx saurabh.dwivedy
The code is dynamic...
Occurence of CAST is not Known..... it may occur 100 times or 0 times
and the Variables inside the cast also not known...
and the code is also not known ....
plz guide me to the solution what i expecting
May 27, 2009 at 10:00 pm
ningaraju.n (5/27/2009)
Thanx saurabh.dwivedyThe code is dynamic...
Occurence of CAST is not Known..... it may occur 100 times or 0 times
and the Variables inside the cast also not known...
and the code is also not known ....
plz guide me to the solution what i expecting
I'm confused, if nothing is known how do you expect to modify it dynamically?
May 28, 2009 at 2:25 am
ningaraju.n (5/27/2009)
Thanx saurabh.dwivedyThe code is dynamic...
Occurence of CAST is not Known..... it may occur 100 times or 0 times
and the Variables inside the cast also not known...
and the code is also not known ....
plz guide me to the solution what i expecting
Declare @Text NVARCHAR(max)
Set @Text='
DECLARE @userid INT
SELECT * FROM [User] WHERE UserId=@userId
DECLARE @Id INT,@IS as NVARCHAR(10)
SELECT CAST(1 BIGINT)
SELECT * FROM [Role]
SELECT CAST(@id NVARCHAR(100))
declare @IG nvarchar(100)
select * from Progressdata where UserId= cast(@UserId NVARCHAR(100))
select cast(@Id decimal(5,2))
'
declare @changed_string nvarchar(max)
set @changed_string = @text
declare @x int, @y int
set @x = 1
while @x 0
begin
set @x = CHARINDEX('cast(', @changed_string, @x)
set @y = CHARINDEX(' ', @changed_string, @x)
if @x 0
begin
set @x = @x + 6
set @changed_string = stuff(@changed_string, @y,1,' as ')
end
end
print @changed_string
OK my friend....I really spent some good amount of time on this. But I finally cracked it. It's not an elegant solution at all. I admit to this much. But I think the problem too was reasonably complex.
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 29, 2009 at 9:37 pm
Thanx Saurabh for ur reply
but i may contain this text also
@text='CAST(@Id nvarchar(100))
CAST ((@pageNo * PageSize - 1) NVARCHAR(100))
CAST (''&Parameter'' NVARCHAR(100))'
when i apply your code it fails...
could u please help me
May 30, 2009 at 1:01 am
ningaraju.n (5/29/2009)
Thanx Saurabh for ur replybut i may contain this text also
@text='CAST(@Id nvarchar(100))
CAST ((@pageNo * PageSize - 1) NVARCHAR(100))
CAST (''&Parameter'' NVARCHAR(100))'
when i apply your code it fails...
could u please help me
This is the best I can think of. The possible variations are infinite. You will need to do at least some data cleaning manually in order for the code to work. This problem cannot be solved unless there is a UNIFORM way to locate WHERE to put the AS
Your problem can be expressed as ... you need to put AS between the Cast Expression and Cast Data type
Cast([expression] (You require AS here) [cast data type])
The way my solution works is that it
1) locates the occurrence of the CAST operator in the string. It does so by looking for CAST(. The basis for looking for Cast( is that I am assuming that the variable or entity that you need to cast would appear directly after the opening bracket.
2) Once it finds CAST(, it then looks for the FIRST SPACE character. I did this because I had to assume that EVERYTHING following CAST( would be the variable or entity that you are casting ... for example
CAST(1 BIGINT) OR
CAST(@userid varchar(max)) etc
3) And finally once it finds the space after Cast([variable or entity to be cast], it inserts an AS character there.
Now your new new @text variable contains Spaces between CAST and the first opening bracket --- like this
@text='CAST(@Id nvarchar(100))
CAST ((@pageNo * PageSize - 1) NVARCHAR(100))
CAST (''&Parameter'' NVARCHAR(100))'
It also contains an enumerated expression [@pageNo * PageSize - 1) which has spaces in the expression. This is BREAKING my assumption for the SPACE character.
If you remove all these spaces (manually) such that THE FIRST SPACE OCCURS AFTER THE CAST and its opening bracket and variables then the code works fine.
For example - try running the code on this (modified) expression
set @text='CAST(@Id nvarchar(100))
CAST((@pageNo*PageSize-1) NVARCHAR(100))
CAST(''&Parameter'' NVARCHAR(100))'
It will work fine.
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply