August 28, 2013 at 4:33 am
Is there a way to sort the table after records are inserted?
Declare @tab Table(name varchar(10), value varchar(10))
Declare @mystring varchar(10)
set @mystring='AA'
insert into @tab
select 'A', 1
union
select 'AA',2
select @mystring=REPLACE(@mystring,name,value) from @tab order by LEN(name) desc
select @mystring
Here is my problem
1. I create a table with two cols Name and Value
2. I insert records into this table using a union statement. (Ex records : {A,1},{AA,2})
3. Now I have a string which I need to be replaced.
Ex: if my string is AA, I need it to be replaced by 2. But now what happen is since in the table A is before AA, it replaces it with two 1’s , ie 11
August 28, 2013 at 4:44 am
The problem is you can't assign multiple values to a single variable. So your "select @mystring=...." statement will only return the last updated value. You also has the order of the parameters in the REPLACE statement wrong. The first parameter is the original value, the second parameter is the string to be replaced.
And the most important rule: an order is only guaranteed if an ORDER BY is used.
Use this code instead:
Declare @tab Table(name varchar(10), value varchar(10))
Declare @mystring varchar(10)
set @mystring='AA'
insert into @tab
select 'A', 1
union
select 'AA',2
select
name
, value
, REPLACE(name,@mystring,value) as new_value
from @tab
order by name
August 28, 2013 at 4:54 am
I have used order by Len(name) desc so that AA will come first, but since order by will apply atlast, it will not work.
One option I have in mind is to use a subquery like
Declare @tab Table(name varchar(10), value varchar(10))
Declare @mystring varchar(10)
set @mystring='AA'
insert into @tab
select * from (select 'A' Name , 1 Value
union
select 'AA',2) A order by LEN(name) desc
select @mystring=REPLACE(@mystring,name,value) from @tab
select @mystring
What you think?
August 28, 2013 at 5:14 am
Because you assign the updated values from a table to a single variable, it will still be possible to get the wrong results. The update on the table could result in multiple rows. Only the last updated row will be visible in the variable.
So the basic of your solution is not correct. Do not assign multiple rows to a single variable. If you really need to assign an updated value to the variable, at least use the TOP 1 clause in combination with an ORDER BY. This will guarantee the row to be returned. Or apply a WHERE clause that will return a single row.
August 28, 2013 at 5:26 am
The same query will work with two rows. The actual scenario has 100’s of data.
August 28, 2013 at 5:32 am
Sanz (8/28/2013)
The same query will work with two rows. The actual scenario has 100’s of data.
And like I said before: only ONE value of this multi-row query wil be stored in the @mystring variable.
August 28, 2013 at 5:39 am
Couple steps back...
What are you trying to do?
Bear in mind that a table has no defined order (by definition). A resultset only has a guaranteed order if an ORDER BY statement is specified on the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2013 at 1:39 pm
select @mystring=REPLACE(@mystring,name,value) from @tab
We don't sort tables. The entire relational world is based on the concept that absolutely no assumptions can ever be made on the physical order of rows in a table. Among other advantages, this frees RDBMS systems to place physical rows however it needs them. So "sort a table" is meaningless. We only sort result sets.
If you want an action to apply to one particular row, you must clearly identify that row. In this simple case, you can change your statement to:
select @mystring=REPLACE(@mystring,name,value) from @tab where name = @mystring
But your statement is more complex than it needs to be. Why do you even need the REPLACE?
select @mystring=value from @tab where name = @mystring
TommCatt
In theory, there is no difference between theory and practice. In practice, there is.
August 30, 2013 at 1:49 pm
You should be able to use a correlated subquery, like so:
SELECT
@mystring,
REPLACE(@mystring, name, value) AS newstring
FROM (
SELECT TOP (1) * FROM @tab WHERE @mystring LIKE '%' + name + '%' ORDER BY LEN(name) DESC
) AS correlated
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".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply