MelvinsWebStuff My Online Notepad

RIGHT
Returns the part of a character string starting a specified number of integer_expression characters from the right.

Example

CREATE TABLE #CustomerNames
(
	CustomerId int,
	CustomerName varchar(25)
)	

INSERT INTO #CustomerNames
SELECT 1, 'Bob Johnson'
UNION ALL
SELECT 2, 'Al Riley'
UNION ALL
SELECT 3, 'Mike Henry'

SELECT RIGHT(CustomerName, 5) FROM #CustomerNames

Results
hnson
Riley
Henry

SELECT *
FROM VTM_DATA_DETAILS
WHERE (PRO_NUMBER = ANY
(SELECT pro_no
FROM vtm_data_combination
WHERE (total_weight = 0) AND (total_charges = 0) AND (total_pieces = 0)))

This query is essentially, imagining PRO_NUMBER is a 3 digit number:

SELECT *
FROM VTM_DATA_DETAILS
WHERE (PRO_NUMBER = 097) OR (PRO_NUMBER = 647) OR (PRO_NUMBER = 324)…..etc.

Until all PRO_NUMBER are selected from vtm_data_combination.

Another way to go about changing the data type varchar to decimal is to change the varchar to money then convert the money data type to decimal. It may not be the best way to do it but it has work for me.

The function returns 1 for numeric values, and 0 for non-numeric values.

CREATE TABLE #CustomerNames
(
	CustomerId int,
	CustomerName varchar(25)
)	

INSERT INTO #CustomerNames
SELECT 1, 'Bob Johnson'
UNION ALL
SELECT 2, 'Al Riley'
UNION ALL
SELECT 3, 'Mike Henry'

SELECT ISNUMERIC(CustomerID), ISNUMERIC(CustomerName) FROM #CustomerNames

Results
1     0
1     0
1     0

Here’s a way to convert a VARCHAR to DECIMAL, wrap the CONVERT in a CASE and IsNumeric function to make sure that it doesn’t cause errors when the varchar data is non-numeric

DECLARE @test VARCHAR(25)

--Numeric
SET @test = '2.55'
SELECT CONVERT(DECIMAL(3,2), CASE WHEN ISNUMERIC(@test) = 1 THEN @test ELSE '0' END)

--Non-Numeric
SET @test = 'Bob'
SELECT CONVERT(DECIMAL(3,2), CASE WHEN ISNUMERIC(@test) = 1 THEN @test ELSE '0' END)

Results
—————————————
2.55

—————————————
0.00

View SQL Server Datatypes

Other Data Types

• sql_variant: Stores values of various
  SQL Server-supported data types, except text, ntext, and timestamp.
• timestamp: Stores a database-wide
  unique number that gets updated every time a row gets updated.
• uniqueidentifier: Stores a globally
  unique identifier (GUID).
• xml: Stores XML data. You can
  store xml instances in a column or a variable (SQL Server 2005 only).
• cursor: A reference to a cursor.
• table: Stores a result set for later
  processing.

Creating

CREATE PROCEDURE sp_GetRecord
@pro_num varchar(10)
AS
SELECT *
FROM DATA_DETAILS
WHERE pro_number = @pro_num

Executing

EXECUTE sp_GetRecord '16571565'

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2"

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998

<?php
print substr(‘abcdef’, 1); // bcdef
print substr(‘abcdef’, 1, 3); // bcd
print substr(‘abcdef’, 0, 4); // abcd
print substr(‘abcdef’, 0, 8 ); // abcdef
print substr(‘abcdef’, -1, 1); // f

// Accessing single characters in a string
// can also be achived using “curly braces”
$string = ‘abcdef’;
print $string{0}; // a
print $string{3}; // d
print $string{strlen($string)-1}; // f

?>

Negative start

<?php
$test_var = substr(“abcdef”, -1); // returns “f”
$test_var = substr(“abcdef”, -2); // returns “ef”
$test_var = substr(“abcdef”, -3, 1); // returns “d”
?>

<%
dim myvar
myvar = "1234567"

response.Write(myvar & " is the variable<br/>")

response.Write(len(myvar) & " characters long<br/>")

response.Write(left(myvar,3) & " are the <b>left</b> three characters<br/>")

response.Write(right(myvar,3) & " are the <b>right</b> three characters<br/>")

response.Write(mid(myvar,3,3) & " : Mid(MyString, StartPos, NumChars)<br/>
%>

Output:

1234567 is the variable

7 characters long

123 are the left three characters

567 are the right three characters

345 : Mid(MyString, StartPos, NumChars)