August 7, 2008 at 3:34 am
Hi Everyone,
I have a table in SQL Server 2000. One coulmn of the table contains numeric data in nvarchar format i.e. 132, 145, 678,979, 321 etc.
What i want to do is insert all the rows of this table in another table, but while inserting the above nvarchar data should get sorted i.e.
Previous values : 132
873
969
after insertion the values should be : 123
378
699
Each numeric value is in one field in a column
Can this be done?
August 7, 2008 at 4:19 am
To be more clear the first table is
Column1 Column2 Column3
132
856
969
After inserting above data into new table it should look like this
Column1 Column2 Column3
123
568
699
old value is 132, new value is 123
old value is 856, new value is 568 and so on
August 7, 2008 at 4:27 am
Hello
You want to sort the data in the string, as opposed to sorting the column.
So the value '132' becomes '123' etc
What is the maximum length of string values found in this column?
Are they all 3 characters like the examples you have shown?
What values are found in the column apart from 0-9?
Is this a one-off?
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 7, 2008 at 5:27 am
-- Create a function
CREATE FUNCTION [dbo].[uftSortString]
(
@String NVARCHAR(100)
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @ReturnValue NVARCHAR(20)
SET @ReturnValue = ''
SELECT @ReturnValue = @ReturnValue + d.TheValue
FROM (SELECT TOP 100 PERCENT CAST(SUBSTRING(@String,number,1) AS CHAR(1)) AS TheValue
FROM dbo.Numbers n
WHERE n.number <= LEN(@String)
ORDER BY SUBSTRING(@String,number,1) ) d
RETURN @ReturnValue
END
-- Use the function like this:
SELECT d.TheValue, [dbo].[uftSortString](d.TheValue)
FROM (
SELECT CAST('132' AS NVARCHAR(6)) AS TheValue UNION ALL
SELECT '873' UNION ALL
SELECT '969') d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 7, 2008 at 5:54 am
HI Chris,
Your solution does not work.
The TOP 100 PERCENT is messing with your results.
If you make TOP 100000 then it works
Which is problem considering you don't know how many rows there are going to be.
I had the same problem.
As a result my function looks like this.
--****************SOLUTION******************
CREATE FUNCTION dbo.udfSortString
(
@String NVARCHAR(100)
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @Tmp TABLE
(string NVARCHAR(100))
DECLARE @Result NVARCHAR(100)
INSERT INTO @Tmp
SELECT SUBSTRING(@String,n,1)
FROM dbo.Tally t
WHERE n <= LEN(@String)
ORDER BY 1
SELECT @Result = COALESCE(@Result, '') + [string]
FROM @Tmp t
RETURN @Result
END
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 7, 2008 at 5:59 am
Hi,
Your query looks very interesting and as soon as I saw this thread, I wanted to have a trial. Here is the solution to your query. For this, you should have a function to split the data. You will get lot more samples through googling. Anyhow this is the one I am using to split the data. Before you run the query create the below function.
***You can use any function which will split the data
Create Function dbo.SplitData
(
@data varchar(500),
@SplitChar varchar(1)
)
RETURNS @SpArray table
(
Id int identity(1,1),
Val varchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitChar,@Data)>0)
Begin
Insert Into @SpArray (Val)
Select
Val = ltrim(rtrim(Substring(@Data,1,Charindex(@SplitChar,@Data)-1)))
Set @data = Substring(@Data,Charindex(@SplitChar,@Data)+1,len(@Data))
Set @Cnt = @Cnt + 1
End
Insert Into @SpArray (Val)
Select Val = ltrim(rtrim(@Data))
Return
END
------------------------------------------
Once this got created, run the below queries for rest of the things
------------------------------------------------------
--your column value will go here
declare @input varchar(10)
--this is to hold the input value by embedding a special character after each digit
--this variable size should be double to your column size
declare @WithExtraChar varchar(20)
--for internal loop
declare @cnt int
set @WithExtraChar=''
set @cnt=0
--Give any test date
set @input='985643127'
while @cnt<=len(@input)
begin
--This will add one extra space after each character so that it can be split easily
set @WithExtraChar=@WithExtraChar+substring(@input,@cnt,1)+' '
set @cnt=@cnt+1
end
--trim any additional spaces
set @WithExtraChar=rtrim(ltrim(@WithExtraChar))
--To hold the final result
declare @result varchar(40)
set @result=''
--this will concatenate all the rows as a single string
select @result=@result+val from dbo.SplitData(@WithExtraChar,' ') order by val
print @result
----------------------------
the final result will be 123456789
--------------------------
You can make this as a function and call in your query as per your requirement. I tested this on SQl200 and 2005 and you can tune this query further. Let me know if you face any issue while executing.
Happy Querying
Thanks
August 7, 2008 at 6:07 am
Christopher Stobbs (8/7/2008)
HI Chris,Your solution does not work.
The TOP 100 PERCENT is messing with your results.
If you make TOP 100000 then it works
Interesting...it works with the (albeit limited) set of sample data I tried. Do you have some figures that it fails with, Chris?
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 7, 2008 at 6:19 am
HI Chris,
I copied your code straight into a a query window and here are the results I code
TheValue(No column name)
132132
873873
969969
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 7, 2008 at 6:21 am
HI khadakrhan,
Good solution.
See if you can challenge yourself and make that code work with a tally table like the other two solutions.
RBAR is bad as you will learn more and more 🙂
:w00t:
[laughing - Chris scores brownie points with Jeff and all other ANTI-RBAR people]
:w00t:
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 7, 2008 at 6:24 am
Christopher Stobbs (8/7/2008)
HI Chris,I copied your code straight into a a query window and here are the results I code
TheValue(No column name)
132132
873873
969969
Thanks Chris...how odd! I've tested it here several times now...here's what I get:
TheValue
-------- -----------
132 123
873 378
969 699
(3 row(s) affected)
I'm on 2k btw same as OP.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 7, 2008 at 6:39 am
Hi Christopher,
what is the value of n..........
I am new to SQL Server.
August 7, 2008 at 6:45 am
ah Chris my bad,
I'm 2005, wierd that the results are different it must do with the Percent and Order by clause combination.
Ankur
n is basically a number, in the Tally table.
The tally table is a table that is just a list of ordered numbers 1,2,3,4,etc.
Here is a sample of how to create one:
--===== Create and populate the Tally table on the fly
SELECT TOP 11000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 7, 2008 at 7:17 am
Christopher Stobbs (8/7/2008)
ah Chris my bad,I'm 2005, wierd that the results are different it must do with the Percent and Order by clause combination.
Thanks for pointing this out Chris, one to watch out for when we upgrade.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 7, 2008 at 4:18 pm
Christopher Stobbs (8/7/2008)
ah Chris my bad,I'm 2005, wierd that the results are different it must do with the Percent and Order by clause combination.
Ankur
What is your default collation?
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply