Check if substring exists within string

I am building a string of the form:

FirstName LastName, FirstName LastName, FirstName LastName,...

and I would like to only add the current name if it isn't already in the string.

The reason I am doing this is because the table I'm working on has multiple entries for the same key but with different values for one of the columns.

The problem is I can't figure out how to check if current name already exists in the name list string that was previously built.

I appreciate your replies. Thank you.

The direct answer is,

CharIndex('Your Searching String', 'Your Main string)

If the result is NON ZERO Then the value is already exists.

Example,

Select Case When CharIndex('Three Four','One Two,Two Three,Three Four') <> 0 Then 'Yes' Else 'No' End --Yes

Select Case When CharIndex('Three Five','One Two,Two Three,Three Four') <> 0 Then 'Yes' Else 'No' End --No

NOTE:

But it is really bad idea to keep the multiple entry values as columns.

Better you can have a one more table where you can make a new row for each names.

The design which you are using is not recommended.

|||

I am pulling data from several tables and in one of them for some reason ( it might be a bug in how that table is generated or in how inserts are being made) I have multiple values for the same column for the same key. Which is generating multiple rows in my query. I cannot change that design. I can only work with it.

Thanks for your answer.

|||hi you can try this options..

SELECT *
INTO #Names
FROM (
SELECT 'John' AS FirstName, 'Doe' AS LastName, 1 AS OtherColumn UNION ALL
SELECT 'John' AS FirstName, 'Doe' AS LastName, 2 AS OtherColumn UNION ALL
SELECT 'Rhamille' AS FirstName, 'Golimlim' AS LastName, 3 AS OtherColumn UNION ALL
SELECT 'Rhamille' AS FirstName, 'Golimlim' AS LastName, 4 AS OtherColumn UNION ALL
SELECT 'Princess Quamille' AS FirstName, 'Golimlim' AS LastName, 4 AS OtherColumn
) Names

SELECT *
FROM #Names

-- option 1
DECLARE @.Names varchar(100)

SET @.Names = ''

SELECT @.Names = @.Names + FirstName + ' ' + LastName + ', '
FROM (
SELECT DISTINCT
FirstName
, LastName
FROM #Names
) Names
ORDER BY
FirstName
, LastName

SET @.Names = LEFT(@.Names,LEN(@.Names) - 1)

-- option 2
DECLARE @.Names2 varchar(100)

SET @.Names2 = ''

SELECT @.Names2 = @.Names2 + (CASE WHEN CHARINDEX(FirstName + ' ' + LastName, @.Names2) > 0 THEN '' ELSE FirstName + ' ' + LastName + ', ' END)
FROM #Names
ORDER BY
FirstName
, LastName

SET @.Names2 = LEFT(@.Names2,LEN(@.Names2) - 1)

SELECT @.Names as FromOption1, @.Names2 AS FromOption2

DROP TABLE #Names

Check if string could be converted to number

I need to check if string could be converted to a int without throwing any
errors.
I need to do something like this
DECLARE @.s varchar(20)
DECLARE @.i int
--if following is possible
@.i=CAST (@.s as int)
--then
SELECT @.1
--else
SELECT 0
if string is not a number I really don't need to deal with it in the first
place.
The real life example of my scenario is checking uniqueness of check number
for bank transactions. If user writes ATM for check number we don't need to
check the uniqueness.
Could it be done?
Thanks,
Shimon.There's a built-in function in SQL - ISNUMERIC. Look it up in Books Online.
However, it has some issues. They are illustrated here:
http://www.aspfaq.com/show.asp?id=2390
..and more! ;)
ML|||Hi Shimon
you can use ISNUMERIC for this
select isnumeric(@.s)
For eg:
if Value of @.s is '123' then the value returned is 1
if Value of @.s is '123a' then the value returned is 0
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Shimon Sim" wrote:

> I need to check if string could be converted to a int without throwing any
> errors.
> I need to do something like this
> DECLARE @.s varchar(20)
> DECLARE @.i int
> --if following is possible
> @.i=CAST (@.s as int)
> --then
> SELECT @.1
> --else
> SELECT 0
> if string is not a number I really don't need to deal with it in the first
> place.
> The real life example of my scenario is checking uniqueness of check numbe
r
> for bank transactions. If user writes ATM for check number we don't need t
o
> check the uniqueness.
> Could it be done?
> Thanks,
> Shimon.
>
>|||Shimon wrote on Wed, 17 Aug 2005 08:25:30 -0400:

> I need to check if string could be converted to a int without throwing any
> errors.
> I need to do something like this
> DECLARE @.s varchar(20)
> DECLARE @.i int
> --if following is possible
> @.i=CAST (@.s as int)
> --then
> SELECT @.1
> --else
> SELECT 0
> if string is not a number I really don't need to deal with it in the first
> place.
> The real life example of my scenario is checking uniqueness of check
> number for bank transactions. If user writes ATM for check number we don't
> need to check the uniqueness.
> Could it be done?
> Thanks,
> Shimon.
Try
DECLARE @.s varchar(20)
DECLARE @.i int
/*set value of @.s here*/
SET @.s = 'test'
IF (ISNUMERIC(@.s) = 1)
SET @.i = CAST(@.s as int)
ELSE
SET @.i = 0
SELECT @.i
You'll get a response of 0. Change 'test' to '1000', you'll get 1000.
Dan|||Oh, one thing I missed in my reply - if the string is numeric, but too large
to fit into an int, you'll get an error, so you should have some check on
the string length to determine if it'll fit, or cast into the largest
numeric datatype.
Dan|||Thanks a lot. Exactly what I needed.
Shimon.
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:880E02E2-DA50-4466-9566-FC429802FB37@.microsoft.com...
> Hi Shimon
> you can use ISNUMERIC for this
> select isnumeric(@.s)
> For eg:
> if Value of @.s is '123' then the value returned is 1
> if Value of @.s is '123a' then the value returned is 0
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Shimon Sim" wrote:
>|||Shimon Sim,
Do you think it is enough using "like" operator?
Example:
select
cast(c1 as int)
from
(
select cast('1080' as varchar(10))
union all
select cast('atm' as varchar(10))
union all
select cast('1081' as varchar(10))
union all
select cast('atm' as varchar(10))
union all
select cast('atm' as varchar(10))
union all
select cast('1082' as varchar(10))
) as t1(c1)
where
c1 not like '%[^0-9]%'
AMB
"Shimon Sim" wrote:

> I need to check if string could be converted to a int without throwing any
> errors.
> I need to do something like this
> DECLARE @.s varchar(20)
> DECLARE @.i int
> --if following is possible
> @.i=CAST (@.s as int)
> --then
> SELECT @.1
> --else
> SELECT 0
> if string is not a number I really don't need to deal with it in the first
> place.
> The real life example of my scenario is checking uniqueness of check numbe
r
> for bank transactions. If user writes ATM for check number we don't need t
o
> check the uniqueness.
> Could it be done?
> Thanks,
> Shimon.
>
>|||Thank you for this note.
Shimon.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:OnUdojyoFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Oh, one thing I missed in my reply - if the string is numeric, but too
> large to fit into an int, you'll get an error, so you should have some
> check on the string length to determine if it'll fit, or cast into the
> largest numeric datatype.
> Dan
>|||I am not sure if it will work in my scenario.
Thank you
Shimon.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D57DFB0A-33B2-4239-AA77-BA15195B6789@.microsoft.com...
> Shimon Sim,
> Do you think it is enough using "like" operator?
> Example:
> select
> cast(c1 as int)
> from
> (
> select cast('1080' as varchar(10))
> union all
> select cast('atm' as varchar(10))
> union all
> select cast('1081' as varchar(10))
> union all
> select cast('atm' as varchar(10))
> union all
> select cast('atm' as varchar(10))
> union all
> select cast('1082' as varchar(10))
> ) as t1(c1)
> where
> c1 not like '%[^0-9]%'
>
> AMB
> "Shimon Sim" wrote:
>

check if cast is possible

I use to import data from DBF Clipper databases into SQL Server. When a table is just imported its date fields have string format. I need to copy their data to tables of database where they ahve to be converted into date. Direct operator INSERT doesn't convert properly (I've not successed in changing default date format so it'll be covertable) but using CAST I can get result of strings like 13.05.1970 0:00:00 as datetime type. But not all records can be coverted this way. For ones can't be converted I've solved to make NULL fields there. But I don't know how to make CAST operation return NULL when convertion isn't possible. The query
INSERT INTO people_temp
(reg_num, surname, stname, patronymic, foreing, gender, birthdate, fam_pos, dwell_type, children, nation, par_not, region, stud_fml, parn_fml,
com_prob, sp_prob, sn_passport, nn_passport, dv_passport, wg_passport)
SELECT STUDENTs_temp.REG_NOM, STUDENTs_temp.FAMILY, STUDENTs_temp.NAME, STUDENTs_temp.PARN_NAME, STUDENTs_temp.INOSTR,

STUDENTs_temp.SEX,
CAST(PSPR_temp.DATA_BORN AS smalldatetime), PSPR_temp.SEM_POL, PSPR_temp.XAR_JT, PSPR_temp.CHILDREN,

PSPR_temp.NATION,
PSPR_temp.SV_ROD1 + PSPR_temp.SV_ROD2 AS Expr1, PSPR_temp.REGION, PSPR_temp.STUD_FML,

PSPR_temp.PARN_FML,
PSPR_temp.OB_STAJ, PSPR_temp.SP_STAJ, PSPR_temp.SN_PASPORT, PSPR_temp.NN_PASPORT, PSPR_temp.DV_PASPORT,
PSPR_temp.WG_PASPORT
FROM STUDENTs_temp INNER JOIN
PSPR_temp ON STUDENTs_temp.REG_NOM = PSPR_temp.REG_NOM
gets an error 'The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value'. Tell me please how can make type casting return NULL if convertion isn't possible.I might try something like so:

Insert Into MyTable(MyDateField)
SELECT (CASE WHEN isDate(DateFieldTobeImported) = 1 Then DateFieldTobeImported ELSE Null End)
From TableBeingImported

If this does not work, please post some sample data.|||Thank you, it works.

Check if a caractere exists in a SQL field

Hi everybody ,
I would like with a Stored procedure check if a caracter exists in a string stored in a SQL server field.
Example :
My field contains the caracters "ABCDEF" ,
I would like to check if "C" is in this string.
Tank Uselect charindex('C', 'ABCDEF') which would return 3. If the first expression is not found in the second, it returns zero.|||Thanks joan but i would like to make the expression 2 a variable wich contains my column name

Remeber the syntax must respect syntax of stored procedure

This is my stored procedure code :
@.level is the caracter i search
@.droits is the caracters contained in my field

IF (@.Level <> "nothing")
BEGIN
SELECT @.droits=droits FROM USERS WHERE UserID=@.UserID
IF NOT SELECT charindex(@.Level, @.droits)


BEGIN
SELECT Progress=2,Errormsg="Pas l'droits."
RETURN
END
SELECT @.AccessNumber = @.AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@.AccessNumber WHERE SessionID=@.SessionID
SELECT Progress=0,userID=@.userID
RETURN
END

can you help me please ?|||DECLARE @.pos
IF (@.Level <> "nothing")
BEGIN
SELECT @.droits=droits FROM USERS WHERE UserID=@.UserID
SELECT @.pos = charindex(@.Level, @.droits)
IF @.pos > 0
BEGIN
SELECT Progress=2,Errormsg="Pas l'droits."
RETURN
END

SELECT @.AccessNumber = @.AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(), AccessNumber=@.AccessNumber WHERE SessionID=@.SessionID
SELECT Progress=0,userID=@.userID
RETURN
END|||It works

Thank U guy : ))

check fields in select statement

hi,

I am getting some fields back from a select statement, how do you check one of the fields and display a string depending on what it is? Is there something like an if statement you can use? for example

select

field1,
field2 /*how do you check to see what it is here and display something depending on what it is*/

from
record

I am trying to see if field2 is a '' or empty string character

thxYou use a CASE statement.

SELECT field1, CASE field2 WHEN '' THEN 'BLANK' ELSE field2 END as Field2 FROM Table

If rather than '' the value might be null, you can use ISNULL

SELECT field1, ISNULL(field2,'BLANK') as field2 FROM Table

|||thank you for the help

check access to DB based on a systable?

I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURS
have a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:

> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS
|||
> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.

check access to DB based on a systable?

I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURShave a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:
> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS|||> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.

check access to DB based on a systable?

I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURShave a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:

> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS|||
> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.

CHARINDEX starting from the end?

Hi,
I have a problem finding the last occurrence of a character in a string.
The charindex seems just able to search from left to right..
Anyone pleas give me a hand.
/Memgardsee if this helps...

Code:
------------------------------
declare @.s varchar(17)
set @.s = '1234|67890A|CDEFG'
select @.s as 'Original String'
select reverse(@.s) as 'In Reverse', datalength(@.s) as 'String Length'
select charindex('|',reverse(@.s)) as 'Location of first ''|'' in Reverse String'
select datalength(@.s) - charindex('|',reverse(@.s)) + 1 as 'Location of last ''|'' in Original string'
------------------------------|||That did help.
Thanks a lot!

Stange that SQL Server dosn't support this with a built in function.
In Oracle you can just add a negative value to scan from the right to left...|||I have found over the years that Oracle and SQL Server both get the job done, just in diffrent ways.

charindex question

I have a field containing a string with '/' in it multiple times.

How can I return the charindex of the last occurance of '/' in the
string?

Regards,
Ciarndeclare @.s varchar(10)
set @.s='as/gf/af/h'
select len(@.s)-charindex('/',reverse(@.s))+1

Madhivanan

Character String Query Doesnt Fill Dataset

I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though.

I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:

set @.FullQuery = 'Select ' + @.FieldsinOrder + ' from tblExample'
exec (@.FullQuery)

This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application.

If I change the query to be a normal select it works. For example:

select * from tblEmample

That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?

try to build your command in ASP and pass it to SQL command command object

like:

Dim

yourCommandAs SqlClient.SqlCommand =New SqlClient.SqlCommand(" 'Select '" + FieldsinOrder + '" from tblExample"', yourconnection)
yourCommand.CommandType = CommandType.Text

Maybe it will work.

When your use Exec to execute query inside query is possible that result from this exec is not visible to ASP.NET code as valid result.

Thanks

Character greater than 'z'

I've searched and have not found any good answers to this. Maybe there
isn't one...
I need to have a string that when sorted with an ORDER BY, it comes after
the letter 'z'.
The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
I know it is best to not rely on this and use some sort of surrogate field
to order the rows, but I cannot do that in this case - the data is being
used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
SharePoint will sort one of it's lists by the BDC column value, but it sorts
only by the values in that column.
In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
the results.
Is there any character I could put at the beginning of the string so it
would fall out last in the query results?
(Remember, it is SharePoint generating the query, so I cannot change the SQL
it uses. I only control the values in the column.)
I assume the column is char or varchar? If so, you can use the value
CHAR(208), for example,
Set NoCount ON
Create Table #FooBar(Foo varchar(10))
Insert #FooBar (Foo) Values ('A')
Insert #FooBar (Foo) Values (Char(208))
Insert #FooBar (Foo) Values ('z')
Select Foo, Ascii(Foo)
From #FooBar
Order By Foo
go
Drop Table #FooBar
You can find all the characters which will sort after 'z' with the following
code
Set NoCount ON
Create Table #FooBar(Foo varchar(10))
Insert #FooBar (Foo) Values ('A')
Insert #FooBar (Foo) Values ('z')
Declare @.i int
Set @.i = 1
While @.i < 256
Begin
Insert #FooBar (Foo) Values (Char(@.i))
Set @.i = @.i + 1
End
Select Foo, Ascii(Foo)
From #FooBar
Where Foo > 'z'
Order By Foo
go
Drop Table #FooBar
Tom
"JD" <yazoo@.newsgroup.nospam> wrote in message
news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
> I've searched and have not found any good answers to this. Maybe there
> isn't one...
> I need to have a string that when sorted with an ORDER BY, it comes after
> the letter 'z'.
> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
> I know it is best to not rely on this and use some sort of surrogate field
> to order the rows, but I cannot do that in this case - the data is being
> used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
> SharePoint will sort one of it's lists by the BDC column value, but it
> sorts only by the values in that column.
> In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
> the results.
> Is there any character I could put at the beginning of the string so it
> would fall out last in the query results?
> (Remember, it is SharePoint generating the query, so I cannot change the
> SQL it uses. I only control the values in the column.)
>
|||JD
select * from #FooBar order by case when foo ='z' then 1 else 2 end
"JD" <yazoo@.newsgroup.nospam> wrote in message
news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
> I've searched and have not found any good answers to this. Maybe there
> isn't one...
> I need to have a string that when sorted with an ORDER BY, it comes after
> the letter 'z'.
> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
> I know it is best to not rely on this and use some sort of surrogate field
> to order the rows, but I cannot do that in this case - the data is being
> used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
> SharePoint will sort one of it's lists by the BDC column value, but it
> sorts only by the values in that column.
> In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
> the results.
> Is there any character I could put at the beginning of the string so it
> would fall out last in the query results?
> (Remember, it is SharePoint generating the query, so I cannot change the
> SQL it uses. I only control the values in the column.)
>
|||As I said, I cannot change the query, since Sharepoint is doing the query.
I can only affect the data in the column.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVGG%2305nIHA.4328@.TK2MSFTNGP03.phx.gbl...
> JD
> select * from #FooBar order by case when foo ='z' then 1 else 2 end
>
> "JD" <yazoo@.newsgroup.nospam> wrote in message
> news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks - it is nvarchar, so I made a few changes to your example and found a
series of Unicode characters that sort after 'z':
set nocount on
create table #FooBar(Foo nvarchar(10))
insert #FooBar (Foo) values ('A')
declare @.i int
set @.i = 900
while (@.i < 1200)
begin
insert #FooBar (Foo) values (nchar(@.i))
set @.i = @.i + 1
end
insert #FooBar (Foo) values ('z')
select Foo, unicode(Foo)
from #FooBar
where Foo > 'z'
order by Foo
drop table #FooBar
"Tom Cooper" <tomcooper@.comcast.no.spam.please.net> wrote in message
news:O6sC0c3nIHA.4832@.TK2MSFTNGP06.phx.gbl...
>I assume the column is char or varchar? If so, you can use the value
>CHAR(208), for example,
> Set NoCount ON
> Create Table #FooBar(Foo varchar(10))
> Insert #FooBar (Foo) Values ('A')
> Insert #FooBar (Foo) Values (Char(208))
> Insert #FooBar (Foo) Values ('z')
> Select Foo, Ascii(Foo)
> From #FooBar
> Order By Foo
> go
> Drop Table #FooBar
> You can find all the characters which will sort after 'z' with the
> following code
> Set NoCount ON
> Create Table #FooBar(Foo varchar(10))
> Insert #FooBar (Foo) Values ('A')
> Insert #FooBar (Foo) Values ('z')
> Declare @.i int
> Set @.i = 1
> While @.i < 256
> Begin
> Insert #FooBar (Foo) Values (Char(@.i))
> Set @.i = @.i + 1
> End
> Select Foo, Ascii(Foo)
> From #FooBar
> Where Foo > 'z'
> Order By Foo
> go
> Drop Table #FooBar
> Tom
> "JD" <yazoo@.newsgroup.nospam> wrote in message
> news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
>

Character greater than 'z'

I've searched and have not found any good answers to this. Maybe there
isn't one...
I need to have a string that when sorted with an ORDER BY, it comes after
the letter 'z'.
The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
I know it is best to not rely on this and use some sort of surrogate field
to order the rows, but I cannot do that in this case - the data is being
used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
SharePoint will sort one of it's lists by the BDC column value, but it sorts
only by the values in that column.
In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
the results.
Is there any character I could put at the beginning of the string so it
would fall out last in the query results?
(Remember, it is SharePoint generating the query, so I cannot change the SQL
it uses. I only control the values in the column.)I assume the column is char or varchar? If so, you can use the value
CHAR(208), for example,
Set NoCount ON
Create Table #FooBar(Foo varchar(10))
Insert #FooBar (Foo) Values ('A')
Insert #FooBar (Foo) Values (Char(208))
Insert #FooBar (Foo) Values ('z')
Select Foo, Ascii(Foo)
From #FooBar
Order By Foo
go
Drop Table #FooBar
You can find all the characters which will sort after 'z' with the following
code
Set NoCount ON
Create Table #FooBar(Foo varchar(10))
Insert #FooBar (Foo) Values ('A')
Insert #FooBar (Foo) Values ('z')
Declare @.i int
Set @.i = 1
While @.i < 256
Begin
Insert #FooBar (Foo) Values (Char(@.i))
Set @.i = @.i + 1
End
Select Foo, Ascii(Foo)
From #FooBar
Where Foo > 'z'
Order By Foo
go
Drop Table #FooBar
Tom
"JD" <yazoo@.newsgroup.nospam> wrote in message
news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
> I've searched and have not found any good answers to this. Maybe there
> isn't one...
> I need to have a string that when sorted with an ORDER BY, it comes after
> the letter 'z'.
> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
> I know it is best to not rely on this and use some sort of surrogate field
> to order the rows, but I cannot do that in this case - the data is being
> used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
> SharePoint will sort one of it's lists by the BDC column value, but it
> sorts only by the values in that column.
> In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
> the results.
> Is there any character I could put at the beginning of the string so it
> would fall out last in the query results?
> (Remember, it is SharePoint generating the query, so I cannot change the
> SQL it uses. I only control the values in the column.)
>|||JD
select * from #FooBar order by case when foo ='z' then 1 else 2 end
"JD" <yazoo@.newsgroup.nospam> wrote in message
news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
> I've searched and have not found any good answers to this. Maybe there
> isn't one...
> I need to have a string that when sorted with an ORDER BY, it comes after
> the letter 'z'.
> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
> I know it is best to not rely on this and use some sort of surrogate field
> to order the rows, but I cannot do that in this case - the data is being
> used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
> SharePoint will sort one of it's lists by the BDC column value, but it
> sorts only by the values in that column.
> In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
> the results.
> Is there any character I could put at the beginning of the string so it
> would fall out last in the query results?
> (Remember, it is SharePoint generating the query, so I cannot change the
> SQL it uses. I only control the values in the column.)
>|||As I said, I cannot change the query, since Sharepoint is doing the query.
I can only affect the data in the column.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVGG%2305nIHA.4328@.TK2MSFTNGP03.phx.gbl...
> JD
> select * from #FooBar order by case when foo ='z' then 1 else 2 end
>
> "JD" <yazoo@.newsgroup.nospam> wrote in message
> news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
>> I've searched and have not found any good answers to this. Maybe there
>> isn't one...
>> I need to have a string that when sorted with an ORDER BY, it comes after
>> the letter 'z'.
>> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
>> I know it is best to not rely on this and use some sort of surrogate
>> field to order the rows, but I cannot do that in this case - the data is
>> being used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
>> SharePoint will sort one of it's lists by the BDC column value, but it
>> sorts only by the values in that column.
>> In ASCII, I could use the tilde (~), but SQL is putting the tilde first
>> in the results.
>> Is there any character I could put at the beginning of the string so it
>> would fall out last in the query results?
>> (Remember, it is SharePoint generating the query, so I cannot change the
>> SQL it uses. I only control the values in the column.)
>>
>|||Thanks - it is nvarchar, so I made a few changes to your example and found a
series of Unicode characters that sort after 'z':
set nocount on
create table #FooBar(Foo nvarchar(10))
insert #FooBar (Foo) values ('A')
declare @.i int
set @.i = 900
while (@.i < 1200)
begin
insert #FooBar (Foo) values (nchar(@.i))
set @.i = @.i + 1
end
insert #FooBar (Foo) values ('z')
select Foo, unicode(Foo)
from #FooBar
where Foo > 'z'
order by Foo
drop table #FooBar
"Tom Cooper" <tomcooper@.comcast.no.spam.please.net> wrote in message
news:O6sC0c3nIHA.4832@.TK2MSFTNGP06.phx.gbl...
>I assume the column is char or varchar? If so, you can use the value
>CHAR(208), for example,
> Set NoCount ON
> Create Table #FooBar(Foo varchar(10))
> Insert #FooBar (Foo) Values ('A')
> Insert #FooBar (Foo) Values (Char(208))
> Insert #FooBar (Foo) Values ('z')
> Select Foo, Ascii(Foo)
> From #FooBar
> Order By Foo
> go
> Drop Table #FooBar
> You can find all the characters which will sort after 'z' with the
> following code
> Set NoCount ON
> Create Table #FooBar(Foo varchar(10))
> Insert #FooBar (Foo) Values ('A')
> Insert #FooBar (Foo) Values ('z')
> Declare @.i int
> Set @.i = 1
> While @.i < 256
> Begin
> Insert #FooBar (Foo) Values (Char(@.i))
> Set @.i = @.i + 1
> End
> Select Foo, Ascii(Foo)
> From #FooBar
> Where Foo > 'z'
> Order By Foo
> go
> Drop Table #FooBar
> Tom
> "JD" <yazoo@.newsgroup.nospam> wrote in message
> news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
>> I've searched and have not found any good answers to this. Maybe there
>> isn't one...
>> I need to have a string that when sorted with an ORDER BY, it comes after
>> the letter 'z'.
>> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
>> I know it is best to not rely on this and use some sort of surrogate
>> field to order the rows, but I cannot do that in this case - the data is
>> being used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
>> SharePoint will sort one of it's lists by the BDC column value, but it
>> sorts only by the values in that column.
>> In ASCII, I could use the tilde (~), but SQL is putting the tilde first
>> in the results.
>> Is there any character I could put at the beginning of the string so it
>> would fall out last in the query results?
>> (Remember, it is SharePoint generating the query, so I cannot change the
>> SQL it uses. I only control the values in the column.)
>>
>

char to total time

I must use a database with strange columns ( I cannot change it) ... one column store time into a string format (char) >>>

4 m 42 s
1 m 10 s

and I must get the total of seconds !!

then how can I get with >>>
4 m 42 s (= 282)
1 m 10 s (= 70)

a total = 352

??

thank youSince no two database engines seem to handle strings quite the same way, which engine are you using? Are the columns limited to just minutes and seconds, or can they add hours, days, fortnights, or other units of time? Is the formatting fixed (always two digit seconds), or can it vary? Are minutes required or optional?

-PatP|||it is for ACCESS 2000

and in the database are only m and s
but I think it is possible to find
4 h 8 m 24 s

nothing else !
maximum are hours

thanks a lot if you can find|||I have tried

Table1 is the table
hms is the column

SELECT Sum(Left([hms],InStr(1,[hms],"m")-1)*60+Mid([hms],InStr(1,[hms],"m")+2,2)) AS sumOfSeconds FROM Table1;

but it doesn't work|||In the VBA Editor, I'd addFunction hms2c(hms As String) As Integer
' ptp 20040404 Covert "[ x h][ y m][ z s]" string to integer seconds

Dim retval As Integer ' return value
Dim c As String ' current character

retval = 0: d = "": hms = LCase(hms)

While hms <> ""
c = Left(hms, 1): hms = Mid(hms, 2)
If 0 < InStr(1, "0123456789", c) Then d = d & c
If "h" = c Then retval = retval + 3600 * Val(d): d = ""
If "m" = c Then retval = retval + 60 * Val(d): d = ""
If "s" = c Then retval = retval + Val(d): d = ""
Wend

hms2c = retval
End FunctionIn the Query, I'd use:SELECT Table1.hms, hms2c([hms]) AS Expr1
FROM Table1;You'll probably find other uses for that function if you deal with these strings much. ;)

-PatP|||yes from outside no problem , and I use VB NET ... but I found the solution on another forum .. only with SQL ! impressive !!

thank you|||Originally posted by castali
yes from outside no problem , and I use VB NET ... but I found the solution on another forum .. only with SQL ! impressive !!

thank you What exactly do you mean by "outside"? everything I've suggested is from pure Access 2000. Unless you are using Office 2003 (aka Office.NET), you can't use VB.NET from within Access.

-PatP|||The solution offered by schlauberger is interesting, but it only works for very limited cases. It will fail if there are hours, or if either the minutes or the seconds are missing. If that works for your needs, enjoy!

-PatP

Char to Base64 conversion

Hello. Anyone know a way to convert a char string into
BASE64? I'm loading some data to Active Directory from
SQL Server using an LDIF script and since there are
carriage returns imbedded, I BELEIVE the only way to do
this is BASE64 encoding. We wrote a function that loops
thru every character and converts it, but when doing a
large select, that encoding function takes too long for
each row.
INPUT CHAR STRING = "Hello World"
OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
Any ideas or hidden functions that do this conversion in
SQL Server? THanks, BruceCheckout the "FOR XML, BINARY BASE64" of the Select statement and see if it
helps you!
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:427201c49029$2a59bec0$a301280a@.phx.gbl...
> Hello. Anyone know a way to convert a char string into
> BASE64? I'm loading some data to Active Directory from
> SQL Server using an LDIF script and since there are
> carriage returns imbedded, I BELEIVE the only way to do
> this is BASE64 encoding. We wrote a function that loops
> thru every character and converts it, but when doing a
> large select, that encoding function takes too long for
> each row.
> INPUT CHAR STRING = "Hello World"
> OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
> Any ideas or hidden functions that do this conversion in
> SQL Server? THanks, Bruce|||wow, NICE!!! I've been doing some reading on that, and it
doesn't appear that it will convert a CHAR type field into
BINARY BASE64 but if you do the convert something like
this below, then you get it back as BASE64, very cool!
BUT, I'd like to strip the XML tags off, probably a
command to get it with just the straight BASE64 code?
Thanks a LOT, that was a great help!!
select top 1 convert(varbinary(8000),name) as 'name_binary'
from sysobjects
for XML RAW, BINARY BASE64
Thanks, Bruce
>--Original Message--
>Checkout the "FOR XML, BINARY BASE64" of the Select
statement and see if it
>helps you!
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:427201c49029$2a59bec0$a301280a@.phx.gbl...
>> Hello. Anyone know a way to convert a char string into
>> BASE64? I'm loading some data to Active Directory from
>> SQL Server using an LDIF script and since there are
>> carriage returns imbedded, I BELEIVE the only way to do
>> this is BASE64 encoding. We wrote a function that loops
>> thru every character and converts it, but when doing a
>> large select, that encoding function takes too long for
>> each row.
>> INPUT CHAR STRING = "Hello World"
>> OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
>> Any ideas or hidden functions that do this conversion in
>> SQL Server? THanks, Bruce
>
>.
>|||Hi Harman, Hi Bruce,
I am currently running into the same problem. I can convert it using the
technique you already mentione, but I can not put it into a variable to
strip off the XML-tags.
Do you have an idea how this could be done?
I tried to select it into a variable (not allowed) and tried to get it tino
a temp-table without success.
Maybe you could be so kind and give me a hint on that ;)
Thanks a lot in advance,
Andreas Bretl
andreas.bretl@.brainlab.com|||Andy, yes, the XML method was sooooooooo close... but no
cigar for the SAME reasons you mentioned! Amazing those
points you found that we did (and a zillion other did
also!). Maybe MS will fix that in the next release...
ANYWAY....... We ended up writing a SQL function, that
went thru the not-so-easy task of converting every
character to BASE64. There may be a better way with the
XML option, but we were ok with the function method for
our purposes. It's also pretty amazing that we have to
jump thru these hoops to stick a carriage return inside
an address block of lines, to send to AD, via an LDIF
script. BASE64 doesn't make for readable code at all,
strange that was needed, but it's working fine after we
figured out all the klooge... Bruce
>--Original Message--
>Hi Harman, Hi Bruce,
>I am currently running into the same problem. I can
convert it using the
>technique you already mentione, but I can not put it
into a variable to
>strip off the XML-tags.
>Do you have an idea how this could be done?
>I tried to select it into a variable (not allowed) and
tried to get it tino
>a temp-table without success.
>Maybe you could be so kind and give me a hint on that ;)
>Thanks a lot in advance,
>Andreas Bretl
>andreas.bretl@.brainlab.com
>
>.
>|||Hi Bruce,
I totally agree, that one was really close at all ;)
I ended up in a COM-DLL I installed on the server where I implemented the
Base64 algo. called via sp_OAMethod.
It works fine but it is strange that such a "simple" thing causes so much
workaround.
Do you think there is a chance to see how you handled it with the SQL
function?
I totally understand if you have to keep it "secret" but I just thought
asking could't harm anybody ;)
Seize the Day
Andy
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:114301c4a5c0$23054bb0$a601280a@.phx.gbl...
> Andy, yes, the XML method was sooooooooo close... but no
> cigar for the SAME reasons you mentioned! Amazing those
> points you found that we did (and a zillion other did
> also!). Maybe MS will fix that in the next release...
> ANYWAY....... We ended up writing a SQL function, that
> went thru the not-so-easy task of converting every
> character to BASE64. There may be a better way with the
> XML option, but we were ok with the function method for
> our purposes. It's also pretty amazing that we have to
> jump thru these hoops to stick a carriage return inside
> an address block of lines, to send to AD, via an LDIF
> script. BASE64 doesn't make for readable code at all,
> strange that was needed, but it's working fine after we
> figured out all the klooge... Bruce
> > >

Char to Base64 conversion

Hello. Anyone know a way to convert a char string into
BASE64? I'm loading some data to Active Directory from
SQL Server using an LDIF script and since there are
carriage returns imbedded, I BELEIVE the only way to do
this is BASE64 encoding. We wrote a function that loops
thru every character and converts it, but when doing a
large select, that encoding function takes too long for
each row.
INPUT CHAR STRING = "Hello World"
OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
Any ideas or hidden functions that do this conversion in
SQL Server? THanks, BruceCheckout the "FOR XML, BINARY BASE64" of the Select statement and see if it
helps you!
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:427201c49029$2a59bec0$a301280a@.phx.gbl...
> Hello. Anyone know a way to convert a char string into
> BASE64? I'm loading some data to Active Directory from
> SQL Server using an LDIF script and since there are
> carriage returns imbedded, I BELEIVE the only way to do
> this is BASE64 encoding. We wrote a function that loops
> thru every character and converts it, but when doing a
> large select, that encoding function takes too long for
> each row.
> INPUT CHAR STRING = "Hello World"
> OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
> Any ideas or hidden functions that do this conversion in
> SQL Server? THanks, Bruce|||wow, NICE!!! I've been doing some reading on that, and it
doesn't appear that it will convert a CHAR type field into
BINARY BASE64 but if you do the convert something like
this below, then you get it back as BASE64, very cool!
BUT, I'd like to strip the XML tags off, probably a
command to get it with just the straight BASE64 code?
Thanks a LOT, that was a great help!!
select top 1 convert(varbinary(8000),name) as 'name_binary'
from sysobjects
for XML RAW, BINARY BASE64
Thanks, Bruce

>--Original Message--
>Checkout the "FOR XML, BINARY BASE64" of the Select
statement and see if it
>helps you!
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:427201c49029$2a59bec0$a301280a@.phx.gbl...
>
>.
>|||Hi Harman, Hi Bruce,
I am currently running into the same problem. I can convert it using the
technique you already mentione, but I can not put it into a variable to
strip off the XML-tags.
Do you have an idea how this could be done?
I tried to select it into a variable (not allowed) and tried to get it tino
a temp-table without success.
Maybe you could be so kind and give me a hint on that ;)
Thanks a lot in advance,
Andreas Bretl
andreas.bretl@.brainlab.com|||Andy, yes, the XML method was sooooooooo close... but no
cigar for the SAME reasons you mentioned! Amazing those
points you found that we did (and a zillion other did
also!). Maybe MS will fix that in the next release...
ANYWAY....... We ended up writing a SQL function, that
went thru the not-so-easy task of converting every
character to BASE64. There may be a better way with the
XML option, but we were ok with the function method for
our purposes. It's also pretty amazing that we have to
jump thru these hoops to stick a carriage return inside
an address block of lines, to send to AD, via an LDIF
script. BASE64 doesn't make for readable code at all,
strange that was needed, but it's working fine after we
figured out all the klooge... Bruce

>--Original Message--
>Hi Harman, Hi Bruce,
>I am currently running into the same problem. I can
convert it using the
>technique you already mentione, but I can not put it
into a variable to
>strip off the XML-tags.
>Do you have an idea how this could be done?
>I tried to select it into a variable (not allowed) and
tried to get it tino
>a temp-table without success.
>Maybe you could be so kind and give me a hint on that ;)
>Thanks a lot in advance,
>Andreas Bretl
>andreas.bretl@.brainlab.com
>
>.
>|||Hi Bruce,
I totally agree, that one was really close at all ;)
I ended up in a COM-DLL I installed on the server where I implemented the
Base64 algo. called via sp_OAMethod.
It works fine but it is strange that such a "simple" thing causes so much
workaround.
Do you think there is a chance to see how you handled it with the SQL
function?
I totally understand if you have to keep it "secret" but I just thought
asking could't harm anybody ;)
Seize the Day
Andy
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:114301c4a5c0$23054bb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Andy, yes, the XML method was sooooooooo close... but no
> cigar for the SAME reasons you mentioned! Amazing those
> points you found that we did (and a zillion other did
> also!). Maybe MS will fix that in the next release...
> ANYWAY....... We ended up writing a SQL function, that
> went thru the not-so-easy task of converting every
> character to BASE64. There may be a better way with the
> XML option, but we were ok with the function method for
> our purposes. It's also pretty amazing that we have to
> jump thru these hoops to stick a carriage return inside
> an address block of lines, to send to AD, via an LDIF
> script. BASE64 doesn't make for readable code at all,
> strange that was needed, but it's working fine after we
> figured out all the klooge... Bruce

Char to Base64 conversion

Hello. Anyone know a way to convert a char string into
BASE64? I'm loading some data to Active Directory from
SQL Server using an LDIF script and since there are
carriage returns imbedded, I BELEIVE the only way to do
this is BASE64 encoding. We wrote a function that loops
thru every character and converts it, but when doing a
large select, that encoding function takes too long for
each row.
INPUT CHAR STRING = "Hello World"
OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
Any ideas or hidden functions that do this conversion in
SQL Server? THanks, Bruce
Checkout the "FOR XML, BINARY BASE64" of the Select statement and see if it
helps you!
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:427201c49029$2a59bec0$a301280a@.phx.gbl...
> Hello. Anyone know a way to convert a char string into
> BASE64? I'm loading some data to Active Directory from
> SQL Server using an LDIF script and since there are
> carriage returns imbedded, I BELEIVE the only way to do
> this is BASE64 encoding. We wrote a function that loops
> thru every character and converts it, but when doing a
> large select, that encoding function takes too long for
> each row.
> INPUT CHAR STRING = "Hello World"
> OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
> Any ideas or hidden functions that do this conversion in
> SQL Server? THanks, Bruce
|||wow, NICE!!! I've been doing some reading on that, and it
doesn't appear that it will convert a CHAR type field into
BINARY BASE64 but if you do the convert something like
this below, then you get it back as BASE64, very cool!
BUT, I'd like to strip the XML tags off, probably a
command to get it with just the straight BASE64 code?
Thanks a LOT, that was a great help!!
select top 1 convert(varbinary(8000),name) as 'name_binary'
from sysobjects
for XML RAW, BINARY BASE64
Thanks, Bruce

>--Original Message--
>Checkout the "FOR XML, BINARY BASE64" of the Select
statement and see if it
>helps you!
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:427201c49029$2a59bec0$a301280a@.phx.gbl...
>
>.
>
|||Hi Harman, Hi Bruce,
I am currently running into the same problem. I can convert it using the
technique you already mentione, but I can not put it into a variable to
strip off the XML-tags.
Do you have an idea how this could be done?
I tried to select it into a variable (not allowed) and tried to get it tino
a temp-table without success.
Maybe you could be so kind and give me a hint on that ;)
Thanks a lot in advance,
Andreas Bretl
andreas.bretl@.brainlab.com
|||Andy, yes, the XML method was sooooooooo close... but no
cigar for the SAME reasons you mentioned! Amazing those
points you found that we did (and a zillion other did
also!). Maybe MS will fix that in the next release...
ANYWAY....... We ended up writing a SQL function, that
went thru the not-so-easy task of converting every
character to BASE64. There may be a better way with the
XML option, but we were ok with the function method for
our purposes. It's also pretty amazing that we have to
jump thru these hoops to stick a carriage return inside
an address block of lines, to send to AD, via an LDIF
script. BASE64 doesn't make for readable code at all,
strange that was needed, but it's working fine after we
figured out all the klooge... Bruce

>--Original Message--
>Hi Harman, Hi Bruce,
>I am currently running into the same problem. I can
convert it using the
>technique you already mentione, but I can not put it
into a variable to
>strip off the XML-tags.
>Do you have an idea how this could be done?
>I tried to select it into a variable (not allowed) and
tried to get it tino
>a temp-table without success.
>Maybe you could be so kind and give me a hint on that ;)
>Thanks a lot in advance,
>Andreas Bretl
>andreas.bretl@.brainlab.com
>
>.
>
|||Hi Bruce,
I totally agree, that one was really close at all ;)
I ended up in a COM-DLL I installed on the server where I implemented the
Base64 algo. called via sp_OAMethod.
It works fine but it is strange that such a "simple" thing causes so much
workaround.
Do you think there is a chance to see how you handled it with the SQL
function?
I totally understand if you have to keep it "secret" but I just thought
asking could't harm anybody ;)
Seize the Day
Andy
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:114301c4a5c0$23054bb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Andy, yes, the XML method was sooooooooo close... but no
> cigar for the SAME reasons you mentioned! Amazing those
> points you found that we did (and a zillion other did
> also!). Maybe MS will fix that in the next release...
> ANYWAY....... We ended up writing a SQL function, that
> went thru the not-so-easy task of converting every
> character to BASE64. There may be a better way with the
> XML option, but we were ok with the function method for
> our purposes. It's also pretty amazing that we have to
> jump thru these hoops to stick a carriage return inside
> an address block of lines, to send to AD, via an LDIF
> script. BASE64 doesn't make for readable code at all,
> strange that was needed, but it's working fine after we
> figured out all the klooge... Bruce

Char compare to String

hi
I using C# to write a ASP.NET page
and I need to compare a string variable to a Char field in the SQL table in
SQL Server 2000
how can I write a SQL statement to do that?\
I try to use "SELECT Name from Employee Where pass="+password
it shows me an "Invalid column name error"
How can I fix that ?
ThanksYou have to put the value between apostrophes.
"SELECT Name from Employee Where pass = '" + password + "'"
in sql is should look like:
select [name] from employee where pass = '@.#WE$%'
AMB
"Lam" wrote:

> hi
> I using C# to write a ASP.NET page
> and I need to compare a string variable to a Char field in the SQL table i
n
> SQL Server 2000
> how can I write a SQL statement to do that?\
> I try to use "SELECT Name from Employee Where pass="+password
> it shows me an "Invalid column name error"
> How can I fix that ?
> Thanks
>
>|||Hi
name is a reserved SQL keyword, so you have put put it in brackets like
Alejandro indicated.
Regards
Mike
"Alejandro Mesa" wrote:
> You have to put the value between apostrophes.
> "SELECT Name from Employee Where pass = '" + password + "'"
> in sql is should look like:
> select [name] from employee where pass = '@.#WE$%'
>
> AMB
>
> "Lam" wrote:
>|||Thanks Mike. I thought the error was related to the comparison, but also was
trying to indicate what you said (my fault, I did it in t-sql, not in
english).
AMB
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> name is a reserved SQL keyword, so you have put put it in brackets like
> Alejandro indicated.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>

Changing the value of the Database Field?

Hi all,

I'm working on Crystal reports in VS2005.

My database fields got the values in integer format whereas I want to display some string instead.
for example... if the database field value is 0, I must display "No Value" and for 1, "Low", for 2, "High".... so on!

I've been trying this using formula fields. But I couldn't get the correct results. the text of the formula fields is not varying according to the database field value.

So, how can I display a value based on the database field value instead of the database field value itself??

Pls help me solving this prob out!

Thank you.What do you currently have as your formula?
Also, look at the help for 'case' or 'switch'.|||Actually, select expressions are more what you're after, like

Select {table.field}
Case 0:
'No Value'
Case 1:
'Low'
Case 2:
'High'
...and so on
default: 'Unknown!';|||I dont know if this works out in your case but you can give a try
right click and select format field
click on the formula editor for suppress
write the formula
if
{dbfield i.e your field} = o then no value else if

{dbfield i.e your field} = 1 then low value else if

{dbfield i.e your field} = 2 then high value.

should work

do reply if it works...
regards|||I dont know if this works out in your case but you can give a try
right click and select format field
click on the formula editor for suppress
write the formula
if
{dbfield i.e your field} = o then no value else if

{dbfield i.e your field} = 1 then low value else if

{dbfield i.e your field} = 2 then high value.

should work

do reply if it works...
regards

Hi...

It worked out, but not with the exact format! I used UnboundString Field and inserted a formula in it for every Database field in my table. My formula contains something similar to what you've suggested!

Any way... I got the result in time and thank you very much for the reply!!