Hi,
I want to get the string representation of a hex number from a
varBinary column of a table.
For example I want to get the output : 'The Hex value is 0xFF'
but
select 'The Hex value is ' + convert(varchar(10), 0xFF)
retruns the ascii charecter for 0xFF
Any idea how do I get the hex form as it is?
thanks.
Supratim
Hi,
Is it possible to get the month name with only an integer representation of the number.
i.e January ,February..... the DATENAME only takes a date as a value.
thanks in advance
I am trying to take a hexadecimal representation of a binary number and convert to the true binary representation so that I can compare it against a binary field in one of my tables.
After reading the documentation it seems I should be able to do this with the CAST or CONVERT function. However it does not appear to be working correctly.
Can you tell me why this T-SQL code produces the wrong binary value:
DECLARE @value binary(16)
SELECT @value = CONVERT(binary, '0764DE49749F274EB924E1552FFE09EC')
PRINT @value
This prints out: 0x30373634444534393734394632373445
That is not correct it should be: 0x0764DE49749F274EB924E1552FFE09EC
Thanks
I 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.
Kinda general question -but before I embark on my own design I thought I ask around. . .
What I want to design is database representation of a schedule of a roster. For instance a school roster. I hope I am not being too general here.
My company is working on a bond derivative portfolio analysis tool andwe're facing a problem that I did not see adequately addressed anywhere in literature. I really did RTFM. I'm very experienced inrelational modelling (10+ years) so this is not a case of notunderstanding the principles. Here is the problem stripped ofirrelevant context. The problem below is simplified for the sake of theexample so don't sweat the details.THE PROBLEM1. There are many types of bonds, each type has a different set ofattributes, different attribute names, different attribute datatypes.For example, bond A has two variables: a yearly interest rate anddate of issue, B has five variables: an interest rate and 4 specificdates on which various portions of principal need to be paid, bond Chas a set of 4 variables: interest rate in period 1, interest rate inperiod 2, the date on which the bond can be put back to the issuer,and two dates on which the bond can be called by the issue. And so on.So, on the first attempt I could represent each bond type as its owntable. For example,create table bond_type_a (rate INTEGER, issue_date DATE)create table bond_type_b (rate INTEGER, principle_date1 DATE,principle_date2 DATE, principle_date3 DATE, principle_date4 DATE)create table bond_type_c (rate1 INTEGER, rate2 INTEGER, put_date DATE,call_date DATE)This is the nice relational approach but it does not work because:2. There are many thousands of bond types thus we would have to havemany thousands of tables which is bad.3. The client needs to be able construct the bond types on the flythrough the UI and add it to the system. Obviously, it would be bad ifeach new type of bond created in the UI resulted in a new table.4. When a user loads the bond portfolio it needs to be very fast. Inthe table per type approach if a user has a 100 different types if bondin the portfolio you would have to do 100 joins. This is a heavilymulti user environment so it's a non-starter. It's impossibly slow.THE SOLUTIONSSo now that we ditched the table per bond type approach we can considerthe followiing solutions (unpleasant from the relational point ofview):1. Name-Value pairs.create table bonds (bond_id INTEGER, bond_type INTEGER, attribute_idINTEGER, value VARCHAR(255))Comment: The client does not like this approach because they want torun various kinds of reports and thus they doe not want the values tobe stored as VARCHAR. They want the DB to enforce the datatype.2. Typed Name-Value pairs.create table bonds (bond_id INTEGER, bond_type INTEGER, attribute_idINTEGER, int_val INTEGER, string_val VARCHAR(255), date_val DATE_Comment: The client does not like this because the table is sparse.Every row has two empty fields.3. Link table with table per data type.create table bonds (bond_id INTEGER)create table bond_int_data (bond_id INTEGER REFERENCES bonds(bond_id),value INTEGER)create table bond_string_data (bond_id INTEGER REFERENCESbonds(bond_id), value VARCHAR(255))create table bond_date_data (bond_id INTEGER REFERENCES bonds(bond_id),value DATE)Comment: This meets most of the requirements but it just looks ugly.4. Dynamic Mappingcreate table (bond_id INTEGER, int_val1 INTEGER, int_val2 INTEGER,date_val1 DATE, date_val2 DATE, string_val1 VARCHAR(255), string_val2VARCHAR(255))Then you have to add some dynamic mapping in your code which willprovide bond specific mapping (say, stored in an XML file). Forexample,For bond_A: yearly_rate maps to int_val1, issue_date maps to date_val1For bond_C: rate1 maps to int_val1, rate2 maps to int_val2, put_datemaps to date_val1, call_date maps to date_val2)Comment: This is very good for performance because when I load aportfolio of different bond types I can pull them all in in one SELECTstatement. However this approach has a problem that the table issparse. The number of fields of each type has to be as high as toaccmodate the most complex bond while simple bonds will only be usingtwo or three.THE QUESTIONS:Are the four approaches I described above exhaustive? Are there anyother that I overlooked?
View 12 Replies View RelatedIn MS Access, for numeric fields, the decimal places shown can be defined as "Auto" meaning that the database will determine the number of decimal places to show based on the content of the field (i.e. 1.0, 0.75, 1.125).
In SQL Server for the same field, it appears that decimal precision is hard coded resulting in a fixed representation (i.e. 1.000, 0.750, 1.125)
Is there a way to make the decimal representation in SQL Server more like Access where trailing zeros are truncated?
Hi,
Here is my sample table creation and insertion script.
I want represent as a summary-result using just one record.
How can I ?
Please note the below red color text.
create table policy
(
id int not null,
name nvarchar(50) not null,
constraint pk_policy primary key(id)
);
create table localhost
(
policy_id int not null,
id int not null,
ip_begin binary(4) not null,
ip_end binary(4) not null,
prefix tinyint not null default 0,
constraint pk_localhost primary key(policy_id,id)
);
create table remotehost
(
policy_id int not null,
id int not null,
ip_begin binary(4) not null,
ip_end binary(4) not null,
prefix tinyint not null default 0,
constraint pk_remotehost primary key(policy_id,id)
);
create table rate
(
policy_id int not null,
inbound int not null,
outbound int not null,
constraint pk_rate primary key(policy_id)
);
-------------
insert into policy values(0,N'policy0');
insert into localhost values(0,0,0xC0A80101,0xC0A80101,24);
insert into localhost values(0,1,0xC0A80A01,0xC0A80B01,0);
insert into remotehost values(0,0,0xACA80101,0xACA80101,0);
insert into remotehost values(0,1,0xACA80A01,0xACA80B01,0);
insert into remotehost values(0,2,0xACA80C01,0xACA80C01,24);
insert into rate values(0,1000,2000);
-------------
select * from policy;
select * from localhost;
select * from remotehost;
select * from rate;
-- result of policy table
0 policy0
-- result of localhost table
0 0 0xC0A80101 0xC0A80101 24
0 1 0xC0A80A01 0xC0A80B01 0
-- result of remotehost table
0 0 0xACA80101 0xACA80101 0
0 2 0xACA80C01 0xACA80C01 24
-- result of rate table
0 1000 2000
------------------------------------------------------
desired result set
id name localhost remotehost rate
-- ---------- ---------------------------------------------------------------- ------------------------------------------- -----------------
0 policy0 (192.168.1.1/24),(192.168.10.1~192.168.11.1) (172.168.1.1),(172.168.12.1/24) 0,1000,2000
descriptin of desired result set :
0. key value is id column and is referenced by each policy_id column.
1. id and name column is the same as policy table's.
2. localhost and remotehost columns are intigration of ip_begin,ip_end,prefix.
3. ip_begin and ip_end should be converted dotted presention from numeric format.
4. if prefix greater than 0, it should be displayed using '/'.
5. if ip_begin and ip_end are equal, show just one ip.
6. if the two ips are different from each other, they separated by '~'.
7. rate field is packed divided by ','.
Our customer (of our ecommerce system) wants to be able to preservedeleted entities in the database so that they can do reporting,auditing etc.The system is quite complex where each end user can belong to multipleinstitutional affiliations (which can purchase on behalf of the user).The end user also has a rich trail of past transactions affiliationsetc. Thus in the schema each user entity is related to many otherswhich in turn relate to yet others and so on.In the past when a user was deleted all of his complex relationshipswere also deleted in a cascading fashion. But now the customer wantsus to add a "deleted" flag to each user so that a user is never_really_ deleted but instead his "deleted" flag is set to true. Thesystem subsequently behaves as if the user did not exist but thecustomer can still do reports on deleted users.I pointed out that it is not as simple as that because the user entityis related to many, many others so we would have to add this "deleted"flag to every relationship and every other entity and thus have"deleted" past purchases, "deleted" affiliations - a whole shadowschema full of such ghost entities. This would overtime degradeperformance since now each query in the system has to add a clause:"where deleted = 0".I assume this is a standard problem since many organizations must havethis need of preserving deleted records (for legal or other reasons).I tried to talk them into creating a simple audit file where all thedeletions will be recorded in XML but they were not too happy withthat.Is there a more satisfying solution to this than have this "deleted"flag?Thanks for your help,- robert
View 9 Replies View RelatedSQL/SERVER 2000:
Data transform task which copies data from a text file to a db table.
Text file field value = 0000000242E (signed decimal)
DB column data type = decimal(11,2)
How do I get this value correctly converted? Getting "invalid data value" error message.
thanks for any help
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,
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
I 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.
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.