|
yeh they are int values. Anyway to do it for int ?
thanks
|
|
|
|
|
AndyInUK wrote: yeh they are int values. Anyway to do it for int ?
Why would you want to? The following values are all the same:
1
01
001
0001
Generally, you'll apply padding purely for display/writing to file purposes, and this should be accomplished in the client code - not in the database.
|
|
|
|
|
yes they mean same but when we try to sort its meaning will change thats y for sorting we need this
|
|
|
|
|
Can you explain? Why do you need to have preceding zeroes in a number to sort?
Simply put, if it is an integer, then
1. you cannot do it
2. you do not need it
|
|
|
|
|
because without zeroes its not sorting in ascending
like i have data
1
2
3
10
11
21
31
without zeroes its sorting like this
1
11
21
31
2
3
|
|
|
|
|
Bullshit if these values are in integer column.
|
|
|
|
|
As Danish said, this is a text sort, you need to check your data type.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
eraser950 wrote: because without zeroes its not sorting in ascending
like i have data
1
2
3
10
11
21
31
without zeroes its sorting like this
1
11
21
31
2
3
That means they are stored as text, and not numbers. Sorting numeric data types gives you the values in the right order.
|
|
|
|
|
True enough. I assumed they were strings because the question doesn't really make sense if they are numeric.
|
|
|
|
|
I assume mysql has similar sorts of functions but I have not had the pleasure, but in T-SQL / SQL Server you could do:
select ID = right('0000000' + convert(varchar(20),ID),7) from MyTable -- ID can be int or varchar
or
select ID = right('0000000' + ID,7) from MyTable -- OK only when ID is varchar.
Likewise you could put this into an update statement if for example you are in the process of converting your ID column from type int to varchar/text etc.
|
|
|
|
|
Helllo
I have a problem while updating data in Sql table,
i have a table named City it contains CityID,CityName,CityShortName
i have created a stored procedure
PROCEDURE [dbo].[UpdateCity]
@CityID int,@CityName nvarchar(50),@CityShortName nvarchar(50)
AS
BEGIN
Declare @ReturnVal nvarchar(20)
SET NOCOUNT ON;
SET XACT_ABORT ON
Begin Try
IF EXISTS (SELECT CityName From City WHERE CityName =@CityName)
BEGIN
SET @ReturnVal ='Already Exists!'
--Return @ReturnVal
END
ELSE
BEGIN
Update City Set CityName=@CityName,CityShortName=@CityShortName
where CityID=@CityID
Set @ReturnVal ='Updated SuccessFully'
--Return @ReturnVal
End
end try
but when i m just editing Shortname its not updating , but its working perfect when i m editing either cityname or cityname and shortname,,
i know that i m doing mistake but where i can't found
second thng how can i show message updated successfully or already exist as i m using asp gridview
Please help me
Thanx
|
|
|
|
|
Here is the source of your problem:
eraser950 wrote: IF EXISTS (SELECT CityName From City WHERE CityName =@CityName)
BEGIN
SET @ReturnVal ='Already Exists!'
--Return @ReturnVal
END
When you do not change the city name, that if clause returns true, and hence you return 'Already Exists!'.
Try:
IF EXISTS (SELECT CityName From City WHERE CityName =@CityName AND CityID<>@CityID)
|
|
|
|
|
|
Thnx its working perfect kindly tell me is this good approach or theres any other method to do this?
second how can show message as i m usng sqldatasource(GridView) to update and delete
|
|
|
|
|
Hello,
FOr example i have the following table
class name
1 m
1 n
1 a
1 b
1 c
2 d
2 e
2 f
I need the following result:
class name
1 m,n,a,b,c
2 d,e,f
I mean grouping by class and all the names in one row
|
|
|
|
|
Use Cursor
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
modified on Monday, September 20, 2010 9:16 AM
|
|
|
|
|
michaelgr1 wrote: all the names in one row
Do you mean "in one field"?
If using Sql Server, you can write a custom aggregation function. There's an example somewhere on MSDN or the help.
It may not be the best solution to this problem, but it's good to know how to do it.
|
|
|
|
|
I need it in a field.
How can i do it? can you show me please?
BTW, I have only read only access to the DB
|
|
|
|
|
Use the pivot command. You can find more information here[^].
|
|
|
|
|
You can use xml path
declare @tmp table (class int, name char)
insert into @tmp values (1, 'm')
insert into @tmp values (1, 'n')
insert into @tmp values (1, 'a')
insert into @tmp values (1, 'b')
insert into @tmp values (1, 'c')
insert into @tmp values (2, 'd')
insert into @tmp values (2, 'e')
insert into @tmp values (2, 'f')
select class,
replace(replace((
select replace(name,' ','*') as 'data()'
from @tmp t2
where t1.class = t2.class
for xml path('')),' ',','),'*',' ') as name
from @tmp t1
group by class
|
|
|
|
|
I hate XML but it is an excellent answer!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I find using STUFF to be slightly easier to read;
SELECT class,
STUFF(
(
SELECT
',' + name
FROM @tmp t2
WHERE t2.class = t1.class
FOR XML PATH('')
), 1, 1, '') AS data
FROM @tmp t1
GROUP BY class
|
|
|
|
|
I'd like to STUFF all xml and it's derivatives where the sun don't shine.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I am looking for query which needs two group by..
table looks like---
falconid priority affecteditem region
1 1 textbook ny
2 1 database ln
3 2 textbook ln
4 1 database zu
5 2 coin ny
6 2 textbook zu
now i want to result sorted by affected item and then count(*) according to region base in set like so that i can count how many ticket opened region vise..
affecteditem ln zu ny
textbook 2 1 0
database 1 1 0
coin 0 0 1
Thanks,
Abhishek
|
|
|
|
|
Look at using a PIVOT
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|