SQL encryption only returning first character

sql substring left of character
charindex
sql server substring
sql substring after character
sql substring before character
find first character in string sql server
sql select everything to the right of a character
sql substring charindex

I have the following query

UPDATE mytable 
SET col1 = ENCRYPTBYPASSPHRASE ('Key', col2) 
FROM mytable

when I decrypt it using

SELECT CONVERT(VARCHAR(20), DECRYPTBYPASSPHRASE ('Key', col1)) 
FROM mytable

The result returned is only the first character, for example if the field contains "Computer" the result is only "C".

col2 is probably nvarchar not varchar. Try

SELECT CONVERT(NVARCHAR(20), DECRYPTBYPASSPHRASE ('Key', col1)) 
FROM mytable

In nvarchar the code points for standard ASCII letters are the same as for ASCII but padded out with a 0x00 byte.

When you cast that to varchar that it is treated as a null character that terminates the string.

LEFT (Transact-SQL), Returns the left part of a character string with the specified number of how many characters of the character_expression will be returned. CHAR returns a NULL value for integer expressions outside this range, or when then integer expresses only the first byte of a double-byte character. Note Some non-European character sets, such as Shift Japanese Industrial Standards , include characters than can be represented in a single-byte coding scheme, but require multibyte encoding.

SUBSTRING (Transact-SQL), Is an integer or bigint expression that specifies where the returned characters start. (The numbering is 1 based, meaning that the first character in� The SQL SELECT TOP Clause. The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

Use CONVERT with data type and size of the value you are encrypting updating. Looks like EncryptByKey does not recognize the data properly as per column schema.

Try as below

ENCRYPTBYKEY(KEY_GUID('<Key Name>'), CONVERT(varchar(20),col1))

SQL Substring function overview, This will ensure that it doesn't matter the length of the data stored in the table column itself, the query will only return the first 15 characters. I have duplicate keys in the JSON text. JSON_VALUE returns only the first key found on the path. How can I return all keys that have the same name? Answer. The built-in JSON scalar functions return only the first occurrence of the referenced object. If you need more than one key, use the OPENJSON table-valued function, as shown in the following

Overview of SQL LOWER and SQL UPPER functions, This article gives an overview of SQL UPPER function and SQL LOWER For example, in [First Name] column, we have Raj (First character In the case of an upper case letter, this function does not perform any operation and return the Transaction log (53); Transparent Data Encryption (TDE) (12). Insert SQL carriage return and line feed in a string. We might require inserting a carriage return or line break while working with the string data. In SQL Server, we can use the CHAR function with ASCII number code. We can use the following ASCII codes in SQL Server: Char(10) – New Line / Line Break Char(13) – Carriage Return

Encrypting Long Strings in SQL Server, Today, I ran into my first instance of encrypting a long - varchar(max) - string in ' This is my SQL Server certificate'; go create symmetric key MySymmetricKey About the only thing you can't do is ignore them. For this demo, I am going to break up the string into 100 character segments. return @xml;. Character Sets, Collations, Unicode. SQL Function and Operator Reference. User-Defined Function Reference. Type Conversion in Expression Evaluation. Operators.

DB2 10 - DB2 SQL - DECRYPT_BINARY, DECRYPT_BIT , The decryption functions can decrypt only values that are encrypted by using The decryption functions return a value that is the result of decrypting encrypted data password-string: An expression that returns a CHAR or VARCHAR value with the function that is specified and the data type of the first argument, as shown� Arguments. The PostgreSQL LEFT() function requires two arguments:. 1) string is a string from which a number of the leftmost characters returned. 2) n is an integer that specifies the number of left-most characters in the string should be returned.

Comments
  • What data type is the column col1 of?
  • column is varbinary(750)
  • Have you tried changing it to VARBINARY(MAX)? Just for the sake of testing if the resulting cipher might need more space.
  • Are you calling a function somewhere and have a parameter defined as VARCHAR only (without explicitly defining a length for the varchar)? In that case, the parameter defaults to a length of exactly 1 character which is typically not what you want - so you should always specify an explicit length for your parameters
  • Read this : social.msdn.microsoft.com/Forums/sqlserver/en-US/…