Converting empty string to Null when inserting/updating

I am using the following query to calculate date differences:
select ........DATEDIFF(d, recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM ....

I have stored all my dates as NVARCHAR because of the issues with localization.
If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table areNULL and they produce the correct result.

Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.
I'd like to use it as a trigger when inserting or updating to convert empty strings to NULL
before the values are inserted.

Thanks guys.

You REALLY should store your dates as a datetime. There is no localization "Problem" with datetimes if you use them correctly, and you can't sort and/or generate good indexes if they are stored in a nvarchar field (Unless you specifically use the YYYYMMDD or YYYY-MM-DD format).

That aside, yes, try NULLIF() like:

INSERT INTO MyTable(col1) VALUES (NULLIF(@.val1,''))

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_nos-nz_3uhy.asp

Converting delimited varchar @parameter for use in NOT IN()

I am creating a stored procedure which is passed a comma delimited string of
ids as a varchar datatype. The param is to be used in an SQL statement such
as:
CREATE PROCEDURE GetFromTable
@.IDs varchar(255)
AS
SELECT * FROM table WHERE iId NOT IN(@.IDs)
GO
The problem is that the iId field is of datatype int, so i get an error
converting the varchar datatype @.IDs to int.
I can not use dynamic SQL as i am not able to give table level access. It
has to be via EXEC rights on the stored procedure.
Any Help?
Thanks
PatrickArrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
AMB
"Patrick Russell" wrote:

> I am creating a stored procedure which is passed a comma delimited string
of
> ids as a varchar datatype. The param is to be used in an SQL statement suc
h
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>
>|||You cannot do this "this way". You'd need to parse your string,
load the values into a TABLE variable and then reference your
table variable:
SELECT * FROM table WHERE iId NOT IN (select myid from @.MyTableVariable)
These two articles will help:
http://www.eggheadcafe.com/articles/20001002.asp
http://www.eggheadcafe.com/PrintSea...asp?LINKID=529
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Patrick,
Parse the @.IDs into rows of a temp table or table variable then use a join
or a subselect. The in operator will not take a variable like this without
building dynamic SQL.
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Hi Patrick.
You could write a function like...
-- pseudo code
create function udtSplitIDs( @.ids varchar(1000) )
returns @.IDsTable table
(
id int
)
as
begin
while (get position of comma)
begin
insert @.IDsTable values( @.strValue )
find next comma
end
return @.IDsTable
end
your select could then be:
SELECT * FROM table
WHERE iId NOT IN(SELECT * FROM udtSplitIDs(@.IDs))
Bryce|||Out of curiousity. A query like that should be avoided if possible in a
high-performance situation due to performance issues, I assume?

Converting Decimal to String W/O Decimal Point

I'd like to convert a Decimal value into a string so that the entire
original value and length remains intact but there is no decimal point.

For example, the decimal value 6.250 is selected as 06250.

Can this be done?select replace(cast (6.250 as varchar),'.','')

JD wrote:
> I'd like to convert a Decimal value into a string so that the entire
> original value and length remains intact but there is no decimal
point.
> For example, the decimal value 6.250 is selected as 06250.
> Can this be done?|||select replace(cast (6.250 as varchar),'.','')|||but length doesn't remain intact|||but length doesn't remain intact|||select replace(space(1)+replace(cast (6.250 as varchar),'.',''),' ','0')|||Thanks!!!

Converting datetime in a string

Hi there,

Can anyone help?

I am trying to convert datetime into a string in the following format


2003-10-09 13:23:15.967



Current syntax:



SELECT '"' + CAST(t.Stamp AS varchar(30)) + '"' AS 'Start'

Any one any ideas?

THANKYOU!!!!

Melanie :)Try this:

select convert(varchar,getdate(),121)sqlsql

Converting Datetime from the Varchar value

I am not sure if this is the correct forum to post to for this but,

I have a stored procedure in the code like so:

dim calensql as string = "sp_scheduleworkfromcal '" & sun & "', '" & mon & "', '" & tue & "', '"
& wed & "', '" & thu & "', '" & fri & "', '" & sat & "', '" & Label1.Text & "', '" & Label2.Text & "', '"
& Label3.Text & "', '" & Label4.Text & "', '" & Label5.Text & "', '" & Label6.Text & "', '" & Label7.Text & "', " & "129"

and then in my stored procedure I have

CREATE PROCEDURE sp_scheduleworkfromcal
(@.sun VarChar(50), @.mon VarChar(50), @.tue VarChar(50), @.wed VarChar(50), @.thu VarChar(50),
@.fri VarChar(50), @.sat VarChar(50), @.dsun VarChar(50), @.dmon VarChar(50), @.dtue VarChar(50),
@.dwed VarChar(50), @.dthu VarChar(50), @.dfri VarChar(50), @.dsat VarChar(50), @.userid int)
AS
Declare @.store datetime
If @.sun != '' begin
 Update servicerequests set date_scheduled=(Convert(datetime, @.dsun)) where trackingnumber=@.sun
 Select @.store = rtrim(retailer) + ' ' + rtrim(storeNumber) from servicerequests where trackingnumber = @.sun
 UPDATE CalendarSchedule SET cal_notes=@.store WHERE cal_date=@.dsun AND userid=@.userid
 IF @.@.ROWCOUNT = 0
 INSERT INTO CalendarSchedule (userid, cal_date, cal_notes) VALUES (@.userid, @.dsun, @.store)
End

and I am getting the error something like
Syntax error converting datetime from character string.

If I change the parameters in the stored procedure to datetime or varchar and get rid of the single quotes, I get incorrect sytax near "/".

I am tracking the sql statement to see where I can fix the problem, but cannot come up with a solution.

can anyone help me out with this one??
Thanks
EricI think the date format you are trying to create is wrong ... At the location ... Try investigating the line :
@.store = rtrim(retailer) + ' ' + rtrim(storeNumber)|||I don't know whether I should use datetime in the values or varchar.
In the two tables
The one the field is a datetime field and in the other table it is a Char(15)

why won't it recognize the sql statement
SQL Statement sp_scheduleworkfromcal '897', '', '', '', '903', '', '', '10/19/2003', '10/20/2003', '10/21/2003', '10/22/2003', '10/23/2003', '10/24/2003', '10/25/2003', 129

thats the trace.

E|||I have tried a variation of your code and am not receiving any errors.

Where exactly are you getting the error? Which line number, and what is the error message exactly?

What are the data types and lengths of the following columns?
-- servicerequests.date_scheduled
-- servicerequests.trackingnumber
-- CalendarSchedule.cal_date

Terri|||servicerequests.date_scheduled datetime(8)
servicerequests.trackingnumber bigint(8)
CalendarSchedule.cal_date datetime(8)

I am getting the error on the
cmd.ExecuteNonQuery() line
and the error is as follows:
System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I changed the values of the textboxes from the page in the sproc from VarChar(50) to datetime as well as the data type in the CalendarSchedule table from Char(15) to datetime and still am getting the same error.

You say you tried a variation of the code? what about it did you change?
are my datatype's wrong?

Thanks
E|||I mean labels from Varchar(50) to datetime - not textboxes

Converting datetime from character string

Hi there,

I have the following code:

Dim CityTown As String = Ctype(Request.Querystring("CityTown"), String)
 Dim Suburb As String = Ctype(Request.Querystring("Suburb"), String)
 Dim SuburbValue As String = Ctype(Request.Querystring("Suburb"), String)
 Dim Rooms As String = Ctype(Request.Querystring("Rooms"), String)
 Dim Rent As String = Ctype(Request.Querystring("Rent"), String)
Dim DateToday = DateTime.Now
Dim mySQL AS String
If suburbValue = "- All -" Then
mySQL = "SELECT propListID, propListExpires, propBuildType, propRoomNumber, propRentPerWeek, propDescription, propCityTown, propSuburb FROM tblPropertyList WHERE [propCityTown]='" & CityTown & "' AND [propRentPerWeek]<= '" & Rent & "' AND [propRoomNumber]='" & Rooms & "'AND [propListExpires] >='" & DateToday & "' "

However I am having trouble getting the Date to work as part of my SQL String. I keep getting the following error:

Syntax error converting datetime from character string.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.
Source Error:
Line 30: MyDA = New SqlDataAdapter (mySQL, myConn)
Line 31: myDS = New DataSet()
Line 32: myDA.Fill(MyDS)
Line 33:
Line 34: dlPropertyListing.Datasource = MyDS.Tables(0)
Source File: K:\details\detailspropertylisting.aspx Line: 32

Any ideas how to resolve this?? I do not want to use Parameters in my statement.

Thanks in advance,

TCMYou can modify the query as :

mySQL = "SELECT propListID, propListExpires, propBuildType, propRoomNumber, propRentPerWeek, propDescription, propCityTown, propSuburb FROM tblPropertyList WHERE [propCityTown]='" & CityTown & "' AND [propRentPerWeek]<= '" & Rent & "' AND [propRoomNumber]='" & Rooms & "'AND [propListExpires] >=getDate()"

Hope this helps.|||and use parameterized queries too.

hth|||Thanks so much for the response.

Worked perfectly.

TCM

Converting Date String to Date

Hi there,

I am trying to convert a date string (YYYYMMDD) to a date (DDMMYY). Using the following formula, I have met with partial success, the day and the year pull through correctly for each entry but the month is always (01).

E.g. (20011031 = 31/01/2001) or (20011201 = 01/01/2001) or (20031208 = 08/01/2003)

My formula is as follows.

//Convert The Status Changed Date

NumberVar xy := tonumber(Left({AUT001.Date Status Last Changed},4));
NumberVar xd := Day(Datevalue (tonumber(Right({AUT001.Date Status Last Changed},2))+1));
NumberVar xm := Month(Datevalue (tonumber(Mid({AUT001.Date Status Last Changed},5,2))+1));

Date(xy,xm,xd)

I am using Crystal 10 and am totally stumped.

Any help would be very much appreciated, thanks in advance.Datevalue of a number ,say x, will return December (30+x), 1899.
So, according to ur formula if the month is 'May' then xm will contain January 5,1900(December (30+5+1),1899).Have added 1 additionally as u have added 1 in ur formula.
So when u take the month fn. it wil return January which is 1(as u have stated).
Hence datevalue of the number(for months at the max. can be 12) will return a date in January only and hence always u get month as January.

tried in crystal 8 and the following worked not sure about Crystal 10

numbervar xy := tonumber(left({@.stringdate},4));
numbervar xd := tonumber(right({@.stringdate},2));
numbervar xm := tonumber(Mid({@.stringdate},5,2));

Date(xy,xm,xd);|||Thank worked fine, thanks very much.

Converting bytes to string

Hi guys,

I'm currently trying to insert image into my SQL db. I have tried a number of methods that were posted online, and so farwith no luck.

My current code reads:

Dim conn As New Data.SqlClient.SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("MainDBConnection").ToString
conn.Open()

Dim cmd As New Data.SqlClient.SqlCommand("SP_SAVEImage", conn)

cmd.CommandType = Data.CommandType.StoredProcedure

Dim sImageName As New Data.SqlClient.SqlParameter("@.sImageName", Data.SqlDbType.VarChar, 50)
sImageName.Value = sImageName

Dim sImageType As New Data.SqlClient.SqlParameter("@.sImageType", Data.SqlDbType.VarChar, 50)
sImageType.Value = fileType

Dim sImageData As New Data.SqlClient.SqlParameter("@.sImageData", Data.SqlDbType.Image, uploadedFile.Length)
sImageData.Value = uploadedFile

cmd.Parameters.Add(sImageName)
cmd.Parameters.Add(sImageType)
cmd.Parameters.Add(sImageData)

Dim reader1 As Data.SqlClient.SqlDataReader

reader1 = cmd.ExecuteReader

Runningthrough debug, everything runs up until the last line, where an erroris caught saying : Failed to convert parameter value from aSqlParameter to a String

I reckon it's to do with the input sImageData being input as a byte array - but I can't seem to find a way around it.

Any help greatly appreciated!!

In http://www.codeproject.com/useritems/images_in_sql_server.asp
private void GuardarImagen(byte[] matriz)
{
this.cmd.CommandText = "insert into tabla(DESCRIPCION, IMAGEN) " +
"VALUES(@.DESCRIPCION, @.IMAGEN)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("Descripcion", this.NombreDeArchivoCorto).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("Imagen", matriz).SqlDbType = SqlDbType.Image;
cmd.ExecuteNonQuery();
}

suggests that yours should be
Dim sImageName As New Data.SqlClient.SqlParameter("@.sImageName", Data.SqlDbType.VarChar, 50)
sImageName.Value = sImageName
Dim sImageType As New Data.SqlClient.SqlParameter("@.sImageType", Data.SqlDbType.VarChar, 50)
sImageType.Value = fileType
Dim sImageData As New Data.SqlClient.SqlParameter("@.sImageData", Data.SqlDbType.Image)
sImageData.Value = uploadedFile

If you are using SQL 2005
cmd.Parameters.AddWithValue("sImageName", sImageName).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("sImageType", sImageType).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("sImageData", uploadedFile).SqlDbType = SqlDbType.Image;

sqlsql

Converting binary to string

is there a way to query a varbinary column and print the readable char in query analyzer?

thanks in advance!

Ryan

No way to do this, because SQL only store the binary data and has no sense for character encoding and structure of the doc.|||

There is a way, here it is...

declare @.pass varchar(30)
declare @.binPass varbinary(8)
set @.binPass = (select password from tblEMP where empid = '123')
set @.pass = (select cast(@.binPass as varchar(20)))
print reverse(@.pass)

I get characters like squares after the text, but I know to disregard them and only look at the text. Anyone know how to get rid of the squares and weird characters? thanks

Ryan

|||

SQL Server will convert varbinary to varchar what it will not convert is varbinary to text. Try the link below for the complete conversion chart and code samples. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

sqlsql

Converting a string to number

Good morning all, ok im trying to convert a string to a numerical value. The problem I am having is that the number has 2 dashes in it. Example and this is always the same 0500-010-95. Is there any way i can change this to a number value so i can create a grouping. The first set is the main the second set is sub-account and the third is department. I am using CR 8.5. I tried converting to a number using the ToNumber({mystringfield}) but it told me it was not a numerical value.

Thanks a lot.

Bryan
Bmcafee@.regio-tile.comTry
ToNumber(replace({mystringfield},'-',''))

Converting a string of binary numbers to a binary datatype

I have a varchar field which holds numeric (binary) data e.g. '00101111' and I want to convert this to a binary data type with the value e.g. 0x00101111

But when I try the following SQL:

select top 5 flag2,convert(binary,flag2) flag2_as_binary from my_table

I get:

flag2 flag2_as_binary
--- -------------------
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000

(5 row(s) affected)

I want some SQL that will return the following (with flag2_as_binary as a real binary datatype):

flag2 flag2_as_binary
--- -------------------
00000000 0x00000000
00000000 0x00000000
00000000 0x00000000
00000100 0x00000100
00000100 0x00000100

(5 row(s) affected)

Thanks.The sql binary datatype is actually displayed hexidecimal base 16 usinge characters 0-9 and A-F, not base 2.

converting a string into a password varbinary field.

Hi,
I have a table that saves user information, name, password etc.
How do I convert the user entered password into a varbinary datatype?convert(varbinary(10), passwordfield) ?
Is this what you are looking for?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan McAuley" <ryan.mcauley@.sympatico.ca> wrote in message
news:TtZ0e.10317$JK1.876077@.news20.bellglobal.com...
> Hi,
> I have a table that saves user information, name, password etc.
> How do I convert the user entered password into a varbinary datatype?
>

converting a string into a password varbinary field.

Hi,
I have a table that saves user information, name, password etc.
How do I convert the user entered password into a varbinary datatype?convert(varbinary(10), passwordfield) ?
Is this what you are looking for?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan McAuley" <ryan.mcauley@.sympatico.ca> wrote in message
news:TtZ0e.10317$JK1.876077@.news20.bellglobal.com...
> Hi,
> I have a table that saves user information, name, password etc.
> How do I convert the user entered password into a varbinary datatype?
>sqlsql

converting a string into a password varbinary field.

Hi,
I have a table that saves user information, name, password etc.
How do I convert the user entered password into a varbinary datatype?
convert(varbinary(10), passwordfield) ?
Is this what you are looking for?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan McAuley" <ryan.mcauley@.sympatico.ca> wrote in message
news:TtZ0e.10317$JK1.876077@.news20.bellglobal.com. ..
> Hi,
> I have a table that saves user information, name, password etc.
> How do I convert the user entered password into a varbinary datatype?
>

Converting a HEX string to its binary representation

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

Chris:

If you are really only dealing with conversion of a constant what you want to do instead of

SELECT @.value = CONVERT(binary, '0764DE49749F274EB924E1552FFE09EC')

is

SELECT @.value = CONVERT(binary, 0x0764DE49749F274EB924E1552FFE09EC)

Dave

|||

You might also be able to use directly:

SELECT @.value = 0x0764DE49749F274EB924E1552FFE09EC

|||

Thanks but my example is just that an example to demonstrate the problem.

The real issues is that I am recieving the binary values as hexadecimal through xml. I need to convert these to their binary representations so that I can query for them. Here is a more complete example:

ALTER PROCEDURE dbo.spc_rels_byRelIds
(
@.ItemsXML TEXT
)
AS

DECLARE @.hDoc int

EXEC sp_xml_preparedocument @.hDoc output, @.ItemsXML

SELECT *
FROM tblRelAttrVals
INNER JOIN
(
SELECT
CONVERT(binary, relID) as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)
) XmlItems
ON grel = relID

EXEC sp_xml_removedocument @.hDoc

Where grel is binary(16) column in the tblRelAttrVals table.

|||

Chris:

Look at this example from earlier this year:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=386406&SiteID=1

|||

Ok so I have this as an example of a stored proc:

if @.hexStr like '%[^abcdef0123456789]%'
BEGIN
set @.b = NULL
return @.b
END

DECLARE @.sql nvarchar(100)
DECLARE @.parms nvarchar(100)

SET @.sql = N'select @.out=0x' + @.hexStr
SET @.parms = N'@.out varbinary(30) output'
EXEC sp_executesql @.sql, @.parms, @.out = @.b output

The problem is I really need a function so that I can do this:

SELECT
dbo.HexToBin(relID) as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)

But functions won't let you call exec sp_executesql from within them.

Does anybody see a way around this?

|||

You didn't mention the version of SQL Server you are using. Below are few optimized solutions that doesn't require dynamic SQL. Use the scalar UDF solution only if you have less number of rows in the table and performance is not important for you. Otherwise, the scalar UDF approach will perform poorly than say inlining the expression in the UDF directly in the SELECT statement.

-- Scalar UDF for SQL Server 2000:

create function hexstr2bin4 (@.hexstr char(8))
returns bigint
with schemabinding
as
begin
return (
((charindex(lower(substring(@.hexstr, 1, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 7))
+ ((charindex(lower(substring(@.hexstr, 2, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 6))
+ ((charindex(lower(substring(@.hexstr, 3, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 5))
+ ((charindex(lower(substring(@.hexstr, 4, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 4))
+ ((charindex(lower(substring(@.hexstr, 5, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 3))
+ ((charindex(lower(substring(@.hexstr, 6, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 2))
+ ((charindex(lower(substring(@.hexstr, 7, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 1))
+ ((charindex(lower(substring(@.hexstr, 8, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 0))
)
end
go

-- Use it like below:

SELECT
dbo.hexstr2bin4(relID) as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)

-- Inline TVF which provides the best performance for using in SELECT statements

-- But this will work only in SQL Server 2005.

create function hexstr2bin4 (@.hexstr char(8))
returns table
as
return (
select ((charindex(lower(substring(@.hexstr, 1, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 7))
+ ((charindex(lower(substring(@.hexstr, 2, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 6))
+ ((charindex(lower(substring(@.hexstr, 3, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 5))
+ ((charindex(lower(substring(@.hexstr, 4, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 4))
+ ((charindex(lower(substring(@.hexstr, 5, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 3))
+ ((charindex(lower(substring(@.hexstr, 6, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 2))
+ ((charindex(lower(substring(@.hexstr, 7, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 1))
+ ((charindex(lower(substring(@.hexstr, 8, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 0)) as bin4
)

go

-- Use it like below:

SELECT
h.bin4 as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)

CROSS APPLY dbo.hexstr2bin4(relID) as h

-- or

SELECT
(SELECT h.bin4 FROM dbo.hexstr2bin4(relID) as h) as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)

|||Thank you very much. I have to support SQL 2000 and SQL 2005. How is the performance for a 16 byte binary value? The SQL 2000 method seems a bit heavy handed, especially being run within a select statement.|||

The performance of the scalar UDF depends on the number of rows on which you are running the SELECT statement to perform the conversion. You need to compare the scalar UDF against the inline approach for your dataset / queries and see. For SQL Server 2005, you can use the expression below in an inline TVF to get the best performance.

Also, below is another way to convert the string to binary. You can extend it to support any arbitrary length upto 8000 bytes.

create function hexstr2bin (@.hexstr varchar(32))

returns binary(32)

as

begin

return

cast((charindex(lower(substring(@.hexstr, 2, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 1, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 4, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 3, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 6, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 5, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 8, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 7, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 10, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 9, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 12, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 11, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 14, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 13, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 16, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 15, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 18, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 17, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 20, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 19, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 22, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 21, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 24, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 23, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 26, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 25, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 28, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 27, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 30, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 29, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 32, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 31, 1)), '0123456789abcdef')-1)*16) as binary(1))
end

Converting a Hex string to binary

I am trying to write a function to convert a hex string to binary. I would like it in a function so I can use it on aggregate data in queries (instead of having to cursor through the data). So, I write my function:

CREATE FUNCTION HexToBinary (@.hexstring char(16)) RETURNS binary(8)
AS
BEGIN
declare @.b binary(8)
,@.sql nvarchar(255)

SET @.sql = N'SET @.b = 0x' + @.hexstring
EXEC sp_executesql @.sql,N'@.b binary(8) out',@.b output
RETURN @.b
END

Then, I try to call my function:

SELECT HexToBinary('E0')

...and I get:

Msg 195, Level 15, State 10, Line 1
'HexToBinary' is not a recognized built-in function name.

However, I can get it to work if I use a slightly different syntax:

declare @.b binary(8)
exec @.b = HexToBinary 'E0'
select @.b

Any thoughts as to what might be going on? Obviously, the lower syntax does not help me call this function in queries, which is really my goal.sp_executesql is indeterministic and is not allowed within a UDF.

Here is the UDF that would give you what you want (courtesy of Itzik):

create function dbo.fn_hexstrtovarbin(@.input varchar(8000))
returns varbinary(8000)
as
begin
declare @.result varbinary(8000), @.i int, @.l int

set @.result = 0x
set @.l = len(@.input)/2
set @.i = 2

while @.i <= @.l
begin
set @.result = @.result +
cast(cast(case lower(substring(@.input, @.i*2-1, 1))
when '0' then 0x00
when '1' then 0x10
when '2' then 0x20
when '3' then 0x30
when '4' then 0x40
when '5' then 0x50
when '6' then 0x60
when '7' then 0x70
when '8' then 0x80
when '9' then 0x90
when 'a' then 0xa0
when 'b' then 0xb0
when 'c' then 0xc0
when 'd' then 0xd0
when 'e' then 0xe0
when 'f' then 0xf0
end as tinyint) |
cast(case lower(substring(@.input, @.i*2, 1))
when '0' then 0x00
when '1' then 0x01
when '2' then 0x02
when '3' then 0x03
when '4' then 0x04
when '5' then 0x05
when '6' then 0x06
when '7' then 0x07
when '8' then 0x08
when '9' then 0x09
when 'a' then 0x0a
when 'b' then 0x0b
when 'c' then 0x0c
when 'd' then 0x0d
when 'e' then 0x0e
when 'f' then 0x0f
end as tinyint) as binary(1))
set @.i = @.i + 1
end

return @.result
end
go

Converting a Hex String to a Varbinary or vice versa

I'm using SQL Server 2005. How can I convert a hex string to a varbinary or the opposite of that?

? For the opposite, the easiest way is to use the built-in (but undocumented) function, fn_varbintohexstr: DECLARE @.x BINARY(16) SET @.x = NEWID() SELECT master.dbo.fn_varbintohexstr(@.x) ... Going the other way around is not quite so straightforward. I usually use dynamic SQL: DECLARE @.y CHAR(34) SET @.y = '0x499104dc92dd27499da9ad8f56dcb437' DECLARE @.x BINARY(16) DECLARE @.sql NVARCHAR(200) SET @.sql = 'SELECT @.x = ' + @.y EXEC sp_executesql @.sql, N'@.x BINARY(16) OUTPUT', @.x OUTPUT SELECT @.x -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Orphaned Zombie@.discussions.microsoft.com> wrote in message news:76360e15-52e8-499f-87b5-44f9eb1f0579@.discussions.microsoft.com... I'm using SQL Server 2005. How can I convert a hex string to a varbinary or the opposite of that?|||

Please take a look at the links below for some options:

Integer value to hex

http://www.umachandar.com/technical/SQL6x70Scripts/Main89.htm

Hex to integer

http://www.umachandar.com/technical/SQL70Scripts/Main29.htm

If you don't have a table of numbers (which the 2nd link uses), you can use code like below:

declare @.t varchar (8)
select @.t = 'FFFFFFFF'
select sum( (charindex( lower( substring( hexstr , number + 1, 1 ) ), hexchars ) - 1) * convert( decimal( 28 , 0 ) , power( 16 , number ) )
)
from
(
select reverse( @.t ) as hexstr, '0123456789abcdef' as hexchars
) as h1
cross join
(select 0 union all select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7) as n(number)
go

You can extend this to generate 64-bit integer/bigint values also.

Converting a date in the form of a string to a datetime format

Hi,

I'm writing some SQL and want to convert the following expression into a date format in the SELECT list:

'01/'&Month(OrderDate)&'/'&Year(OrderDate)

Basically, I want to look at each order date, convert that date to the first of that month and GROUP BY this expression.

There is a CONVERT function, but I'm new to all this and can't seem to get it to work.

Any help would be gratefully received!

Cheers!

Keith

you can group by without having to transform the date and that is

done like the example below

use northwind
select year(orderdate) as year, month(orderdate) as month , count(*)as ordercount from orders
group by year(orderdate),month(orderdate)
order by 1,2

|||

Try,

select '01/'+convert(varchar(2), month(OrderDate))+'/06' from Table

|||the year shouldn't be static... just add convert(varchar(2), year(OrderDate)) in place of 06|||

Cheers!

I'll give these a try.

Is the plus symbol '+' used in place of '&' to concatenate strings together in transact SQL then?

Also, I looked up varchar(2) in my ASCII list and it denotes 'start of text'. Why is this necessary out of interest?

Many thanks indeed!

Keith

|||

Ok, I've given the following a go:

SELECT '01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate)) AS 'OrderMonth'

It runs, but this is the result:

01/12/*

For some reason, the year is represented by a *. Any ideas why this may be?

Also, the expression as it is above still brings back a date (albeit with a * for the year) in a string format. Ideally, I want to convert all of that to a date format so it behaves like a date. To that end, I then took the above a bit further and attempted this:

SELECT CONVERT(Datetime,'01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate))) AS 'OrderMonth'

This basically encapsulates the exact expression above within a 'top level' CONVERT statement to convert it to datetime. I get the following run time error message though:

'Syntax error converting datetime from character string'. It's probably due to the *. To someone on here, it's probably obvious what I'm doing wrong.

Any ideas?

Many thanks!!!

|||

What about this:

SELECT DATEADD(mm, DATEDIFF(mm,0,orderdate), 0) as firstdayofOrdersDates , count(*)as ordercount from orders

group by DATEADD(mm, DATEDIFF(mm,0,orderdate), 0)

|||

Hi limno,

Whilst your illustration doesn't quite bring back what I need, it has pointed the way to exactly what I need! See below what I have used:

SELECT DateAdd(Day,-Day(OrderDate)+1,OrderDate) AS 'OrderMonth'

Essentially what this is doing is dedecting the number of days into the month for a particular order date to get back to the last day of the previous month. I then add on 1 to give me the first day of the current month. Using this in the GROUP BY statement then nicely groups all my orders by month!

So really, I didn't need to do any converting of string expressions after all. I can't believe I didn't think of this before. It must have been staring me in the face!

Cheers for your help!

Converting a date in the form of a string to a datetime format

Hi,

I'm writing some SQL and want to convert the following expression into a date format in the SELECT list:

'01/'&Month(OrderDate)&'/'&Year(OrderDate)

Basically, I want to look at each order date, convert that date to the first of that month and GROUP BY this expression.

There is a CONVERT function, but I'm new to all this and can't seem to get it to work.

Any help would be gratefully received!

Cheers!

Keith

you can group by without having to transform the date and that is

done like the example below

use northwind
select year(orderdate) as year, month(orderdate) as month , count(*)as ordercount from orders
group by year(orderdate),month(orderdate)
order by 1,2

|||

Try,

select'01/'+convert(varchar(2),month(OrderDate))+'/06'from Table

|||the year shouldn't be static... just add convert(varchar(2), year(OrderDate)) in place of 06|||

Cheers!

I'll give these a try.

Is the plus symbol '+' used in place of '&' to concatenate strings together in transact SQL then?

Also, I looked up varchar(2) in my ASCII list and it denotes 'start of text'. Why is this necessary out of interest?

Many thanks indeed!

Keith

|||

Ok, I've given the following a go:

SELECT '01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate)) AS 'OrderMonth'

It runs, but this is the result:

01/12/*

For some reason, the year is represented by a *. Any ideas why this may be?

Also, the expression as it is above still brings back a date (albeit with a * for the year) in a string format. Ideally, I want to convert all of that to a date format so it behaves like a date. To that end, I then took the above a bit further and attempted this:

SELECT CONVERT(Datetime,'01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate))) AS 'OrderMonth'

This basically encapsulates the exact expression above within a 'top level' CONVERT statement to convert it to datetime. I get the following run time error message though:

'Syntax error converting datetime from character string'. It's probably due to the *. To someone on here, it's probably obvious what I'm doing wrong.

Any ideas?

Many thanks!!!

|||

What about this:

SELECTDATEADD(mm,DATEDIFF(mm,0,orderdate), 0)as firstdayofOrdersDates ,count(*)as ordercount from orders

groupbyDATEADD(mm,DATEDIFF(mm,0,orderdate), 0)

|||

Hi limno,

Whilst your illustration doesn't quite bring back what I need, it has pointed the way to exactly what I need! See below what I have used:

SELECT DateAdd(Day,-Day(OrderDate)+1,OrderDate) AS 'OrderMonth'

Essentially what this is doing is dedecting the number of days into the month for a particular order date to get back to the last day of the previous month. I then add on 1 to give me the first day of the current month. Using this in the GROUP BY statement then nicely groups all my orders by month!

So really, I didn't need to do any converting of string expressions after all. I can't believe I didn't think of this before. It must have been staring me in the face!

Cheers for your help!

sqlsql

Converting @myString to @myInt

Here's a fun one. BOL says that using CONVERT or CAST to change a string into a number won't work.

The following "works" and I'm assuming it is because the compiler can verify that the string value is in fact a number, even with the Unicode N:

declare @.col2 int

select @.col2 = convert (int, N'12345')

======================================

Here's what I need to work, or I need a creative work-around if someone has an interesting idea:

declare @.col2 int,
@.myString nvarchar(100)

select @.col2 = '12345'
select @.myString = convert(int, @.col2)

Msg 245, Level 16, State 1, Line 67
Conversion failed when converting the nvarchar value '12345' to data type int.

I'm assuming that since the compiler cannot pre-determine the value of @.col2 that it won't allow this.

I understand the responsibility to ensure that the converted string is actually a number. I can put something together as a pre-check for this.

Any suggestions or creative work-arounds would be helpful!

Thanks!

Doug B

> declare @.col2 int,

> @.myString nvarchar(100)

>

> select @.col2 = '12345'

> select @.myString = convert(int, @.col2)

>

> Msg 245, Level 16, State 1, Line 67

> Conversion failed when converting the nvarchar value '12345' to data

> type int.

This works fine for me on SQL Server 2000 (8.00.2151) and SQL Server 2005

(9.00.1399).

A

|||Thanks so much...my example was an attempt to simplify the question, so the code wasn't "exactly" what I was running. Once I realized it should have worked, I found a bug in a substring elsewhere.

Looks like you can convert from a string to a number after all, as long as it is a valid number.

Thanks!

DB|||

> Thanks so much...my example was an attempt to simplify the question,

Don't do that. The people here who are trying to help do not need it.

Whether your SQL is 4 lines or 40, we are still going to cut & paste into QA

or SSMS and run it, and then we will find the actual problem instead of

wasting time asking for more information...