Hi,
ORDER BY will sort string (varchar) field which "11" < "2", is there any way I can sort them as number so "11" > "2"?
what if there is character inside, like "11a", "2b", in this case, it should sort like, "1" "2" "11" "1a" "2a" "11a" "1b" ....
Is this possible?
also, I tried to google this but not find anything help. what's best keyword to use?
Thank you,
Wes
In MSSQL, is there a way to count the number of instances of asubstring within a string, so that I can sort by that?For example:table tst contains one column: tst_dataif tst_data = "the man with the plan"I'd want a function that counts the occurances of "the"count_substring(tst_data,'the') = 2Basically, I'm making a search engine and I'd like to put the mostrelavent hits at the top of the page.
View 2 Replies View RelatedI have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234
123
12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
Hello,
I am trying to extract from some strings like the following strings the number and order them by that number:
Box 1
Box 2
Box 3
Box 20
Box 21
...(and so on)
The problem I am having is that I already extracted the number using
Substring([field],[starting position],[lenght])
but the output seems to be in a string format, so the order is not in an ascending order.
Thanks for any suggestions.
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
I have a string in form "abcdefg 12355 ijklmn"Now I want get only the number 12355 within the string !!Is there any function available in T-SQL of Sql server 2K??Thanksfor any help
View 6 Replies View RelatedI've just upgraded from Access to SQL 2000 and am having problems with a line of SQL code.
In Access I sorted on a text column of part numbers where a number within the text was the important sort value.
Text was typically "AB 1234 A" "AB 34 B" "AB 73000"
The important sort has to be on the numeric value of the part number.
Access Code that worked was:
ORDER BY Val(MID(Parts.PartNo,4,6))
Seeing both Val() and MID() are not acceptable in SQL 2000 I ended up using
Substr(Parts.PartNo,4,6) but this returns a string sort where 100 appears before 25 etc.
Can anyone help point me in the right direction?
Many thanks
Peter
Quick question I hope:
Is there a way to convert a string into a unique number. I have tried using checksum(str) but this appears not to be unique.
For example:
select checksum('KEY AND KEY')
select checksum('COPENBARGER AND COPENBARGER')
both yield -2027749374.
Can someone suggest an alternative function?
Many thanks :)!
Hi
ID=2763&Department=SLA
How do I get number between 'ID=' and '&' from the above string. The digits vary from 2 to 6 digits
Advance Thanks
I search the help for T-sql, still don't know how to convert a number, for example 12345 to a string with formating like 12,345
Thanks for your help.
Hi guys,
I have 2 tables:
Unit( unit_id integer, unit_name varchar(20) )
Part( part_id integer, part_name varchar(20) )
Then I have a join table between the two
Unit_Part( unit_id, part_id )
Each unit NAME and part NAME is unique. So what kind of performance would I be losing if I were to do something like:
PROCEDURE addPartToUnit( VARCHAR(20) sPartName, VARCHAR(20) sUnitName )
-- Now in this procedure i'll just get the id's via the names from a select statement
Versus
PROCEDURE addPartToUnit( nPartId INTEGER, nUnitId INTEGER )
-- In this proc, i directly input the primary keys of the table so no lookup is necessary
The reason is that I would like for developers to be able to write code without having to know the id's.
What do you think?
I have a column which keeps numeric data in form of String, but I want to convert it to number e.g. from "1001" to 1001,
Which function I can use to do it?
[URL] .....
I have table (log)as below
ID---Qstring----------------------------------------------ProID
1----ID=25375
2----ID=23382
3----lngSessionId=134513044&pid=25375
52---lngSessionId=133466044&pid=27890
67---tduid=dc79b&url=http://www.mydomain.co.uk/proddetails.asp?id=186
How do I read only the number starts with ID and pid from the above string and insert into column 'ProID' of the same row? It is log table tracking all the visiters to the web site. The number next to IDs and pids are productIDs. So if I manage to get product ID to the column ProId then I can hit against other table to find out which product the customer clicked.
I planned to do the above said by trigger. Please help
how to convert number to string in sqlservereg:- 10 -> ten display*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedHi,I want to get the string representation of a hex number from avarBinary column of a table.For example I want to get the output : 'The Hex value is 0xFF'butselect 'The Hex value is ' + convert(varchar(10), 0xFF)retruns the ascii charecter for 0xFFAny idea how do I get the hex form as it is?thanks.Supratim
View 2 Replies View RelatedI am trying to convert some strings to number datatype. Some numbers are like this.
1,45.45-
21.21-
4,611.20-
Is it possible to convert these type of strings to number datatype.
Thank you.
hi,
I have a bit string like '000111010101101110000111' , how to convert it to varbinary format like 0x1D5B83?
Thanks.
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
Lets say I have a table named Projects with the data below.Y07001Y07002Y07003Y07010Y07011I want to pick up the last 3 numbers and add a one to it so.
Declare @Count as varchar(3)
SELECT Top 1 @Count = Right(Project, 3)+1 FROM Projects Order by ID desc
SELECT @Count
This is what I am using. It picks up the last entry, add a one to it and gives me 12 in return. What i want is 012, not 12
Hi,
In short:
I want my following problem to work with a LIKE instead of exact match and if possible be faster. (currently 4s)
Problem:
I got a set of rows with varchar(50), spread out over multiple tables.
All those tables relate to a central Colour table.
For each of the columns, I want to match the values with a set of strings I insert and then return a set of Colour.Id
E.g: input: 'BLACK', 'MERCEDES', '1984'
Would return colour ids "025864", 45987632", "65489" and "63249"
Because they have a colour name containing 'BLACK' or are on a car from 'MERCEDES' or are used in '1984'.
Current Situation:
I) Create a table containing all possible values
CREATE TABLE dbo.CommonSearch(
id int IDENTITY (1, 1) NOT NULL,
clr_id int,
keyWord varchar(40),
fieldType varchar(25)
And fill it with all the values (671694 rows)
)
II) Stored Procedure to cut a string up into a table:
CREATE FUNCTION dbo.SplitString
(
@param varchar(50),
@splitChar char = ''
)
RETURNS
@T TABLE (keyWord varchar(50))
AS
BEGIN
WHILE LEN( @param ) > 0 BEGIN
declare @val varchar(50)
IF CHARINDEX( @splitChar, @param ) > 0
SELECT @val = LEFT( @param, CHARINDEX( @splitChar, @param ) - 1 ) ,
@param = RIGHT( @param, LEN( @param ) - CHARINDEX( @splitChar, @param ) )
ELSE
SELECT @val = @param, @param = SPACE(0)
INSERT INTO @T values (@val)
END
RETURN
END
III)Stored Procedure to query the first table with the second one
CREATE PROCEDURE [dbo].[GetCommonSearchResultForTabDelimitedStrings]
@keyWords varchar(255) = ''
AS
BEGIN
SET NOCOUNT ON;
select clr_id, keyWord, fieldType
from dbo.commonSearch
where keyWord in (select * from splitString(@keyWords, ''))
END
So, how can I use a LIKE statement in the IN statement of the last query.
Furthermore, I was wondering if this is the best sollution to go for.
Are there any better methods? Got any tuning tips to squeeze out an extra second?
I've got a string of numbers, say 123456 (the actually number is 12 digitslong). I need to calculate the sum of each individual number in the stringof numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).Thanks
View 5 Replies View Related
Hello
I am importing from a comma delimited text file. A certain string column has values that represent numbers, such as "US$ 20", "30", "40 - 50". I would like to import the number, and if the cell does not contain a string representing a number (example: "I don't know"), a NULL value should be imported instead for that cell.
Thanks a lot.
Hello experts,
Suddenly I find myself in need of a SQL search that ONLY starts at a record. I need to page through the existing records, but start at the users choice.
Hi,
I was wondering what would be the best way to remove special characters like, '-', '&' '(',')','#','*', etc... from a number string. To be specific a phone Number string where the string is >= 10.
Thanks, Mark
Hi all,
@str varchar(500)
select @str = 'abcd,efgh,i,jklmn,opsqrtuv'
For the above string, which string function can be used to find the number of occurences of a particular character, for example, ','? For this example, the answer should be 4. Is there any built-in function in SQL to do this?
Thanks in advance,
-Praveena
In t-sql 2008 r2, I would like to know how to select a specific string in a varchar(50) field. The field in question is called 'CalendarId'.
This field can contain values like:
xxIN187 13-14 W Elem
HS321 13-14 D Elem
IN636 13-14 C Elem
030 13-14 clark middle.
What I am looking for is the first position that contains a number value for the length of 3. Thus what I want are values that look like the following: 030, 636, 187.What I know that I want is substring(CalendarId,?,3).The question mark is where I want the starting location of a number value (0 to 9) of the value in CalendarId . I tried pathindex but my syntax did not work.
I have a query which runs exactly as I want it to. Let's say it returns this in a table:
IDNumber
123
212
312
I want to change (find and replace?) all the number 12s in the 'Number' column for a string. I want to change the 12s to the word: HelloWorld. I don't want to update the table, merely the table that is being returned in the query.
I would like to convert a number into fixed format string. Say for example if I have number 5, I would like to show it as four charactered string:
0005.
In case if I have 33, then I would like to have a result like '0033'. Please let me know the string functions with which I can acheive this.
I'm new to SQL and I've been trying this for a while now.
I have let's say a loop of numbers from one to ten. It appears like this:
1
2
3
4
5
What I want to do is to have it appear on one column like this: 12345, the problem is I can't seem to figure out if I need to only have one variable or I'm missing something else, I figured you need to convert them into a string character but I'm still unable to do it.
How I can split the half hour in number from this string?
20130329070000 (it's varchar)
Easy for the date
cast (left(20130329070000,8)as date)
I can achieve even the Half Hour
select
left(SUBSTRING('20130329070000',9,4),2)+':'+RIGHT(SUBSTRING('20130329070000',9,4),2)
But what I need is the Half Hour in numeric format, so 00:00
should be 1, 00:30 should be 22, 01:00
should be 3, 01.30 should be 4......23:30 should be 24.
I already did but I can't find the piece of code I used.
add a number to the end of an ID to create a series.For example, I have an EventID that may have many sub events. If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:
312061
312062
312063
312064
How can I check what the EventID is, then concatenate a sequence number by the EventID?
I have written a query to search for a string in an expression by the number of it's appearance. Script is like this:
DECLARE @Expression VARCHAR(8000) = 'abcd_e_fgh',
@SearchString VARCHAR(10)= '_',
@OccuranceNumber SMALLINT = 1
DECLARE @SearchIndex INT = 0, @SearchIndexPrevious INT = 0, @Sno INT = 0
WHILE @Sno < @OccuranceNumber BEGIN
[Code] .....
Here i'm trying to search "_" in expression "abcd_e_fgh" where it is appearing for first time. it gives me 5 correctly. Now when i change the @OccurenceNumber to 2 or 3, it gives correct values 7 and -1 respectively. However now when i change it to 4, it gives me 5. So when it's trying to check for fifth appearance of "_", it's not actually giving 0 or -1 but repeating the value 5.
How do I display numeric data as text string?? I need the report to spell out the number. 1 would read as One, 2 as Two ect. For example writing the amount on a check. Need to do in SSRS.
View 6 Replies View Related