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 […]
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 […]
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 […]
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 […]
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}; […]
<% 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 […]