SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is there a way to sort the table after records are inserted?


Is there a way to sort the table after records are inserted?

Author
Message
Sanz
Sanz
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 982
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

Sanz
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4420 Visits: 3670
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



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Sanz
Sanz
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 982
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?

Sanz
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4420 Visits: 3670
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.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Sanz
Sanz
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 982
The same query will work with two rows. The actual scenario has 100’s of data.

Sanz
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4420 Visits: 3670
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.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88182 Visits: 45277
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Thomas.Carr 15137
Thomas.Carr 15137
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 17
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.
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7971 Visits: 7160
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search