Showing posts with label String Text. Show all posts
Showing posts with label String Text. Show all posts

Monday, 14 November 2016

SQL SERVER REVERSE FORMULA

SQL SERVER REVERSE FORMULA

-- EXAMPLE

SELECT REVERSE('STEP') AS[DISPLAY THE VALUE WITHIN THE COLUMN IN REVERESE ORDER];


SQL SERVER REPLICATE FORMULA

SQL SERVER REPLICATE FORMULA

-- THEORY

-- SELECT REPLICATE('VALUE ', [NUMBER OF TIMES TO REPLICATE VALUE]) AS [ALIAS NAME];

-- EXAMPLE

SELECT REPLICATE('TEST ',7) AS [DISPLAY REPLICATION OF THE WORD TEST 7 SEVEN TIMES];


SQL SERVER REPLACE FORMULA

SQL SERVER REPLACE FORMULA

-- THEORY

-- SELECT REPLACE([COLUMN],'VALUE','REPLACEMENT VALUE') AS [COLUMN ALIAS NAME], *

-- FROM [DATABASE NAME].[DATABASE OBJECT].[TABLE NAME] AS [TABLE ALIAS NAME];

-- EXAMPLE

SELECT REPLACE(PC.EnglishProductCategoryName,'Bikes','Bike') AS [DISPLAY REPLACEMENT FOR BIKES TO BIKE], *
FROM AdventureWorksDW2012.dbo.DimProductCategory AS PC;


SQL SERVER PATINDEX FORMULA

SQL SERVER PATINDEX FORMULA

-- THEORY

-- SELECT PATINDEX('ENTER TEXT TO SEARCH%', [COLUMN NAME TO SEARCH]) AS [ALIAS NAME], *

-- FROM [DATABASE NAME].[DATABASE OBJECT].[TABLE NAME] AS [ALIAS NAME]

-- Where PATINDEX('ENTER TEXT TO SEARCH%', [COLUMN NAME TO SEARCH]) = 1;

-- NOTE: 1 EQUALS TRUE AND 0 EQUALS FALSE OR (NOT TRUE)

-- EXAMPLE

SELECT PATINDEX('Bike%', PC.EnglishProductCategoryName) as [DISPLAY TRUE OR 1 FOR PATTERN INDEX MATCH ROW], *
FROM AdventureWorksDW2012.dbo.DimProductCategory as PC
where PATINDEX('Bike%', PC.EnglishProductCategoryName) = 1;

SQL SERVER LTRIM FORMULA

SQL SERVER LTRIM FORMULA

-- THEORY

-- SELECT LTRIM(' VALUE WITH SPACES') AS [ALIAS NAME OF FIELD VALUE]

-- EXAMPLE

SELECT LTRIM(' TRIM THE SPACES ON THE LEFT OF THIS VALUE') AS [LEFT TRIM FORMULA]

SQL SERVER LOWER CASE FORMULA

SQL SERVER LOWER CASE FORMULA

-- THEORY

-- SELECT LOWER('ENTER UPPER CASE VALUE OR SELECT FIELD FROM TABLE WITH UPPER CASE VALUE') AS [ALIAS NAME OF FIELD VALUE];

-- EXAMPLE

SELECT LOWER('CONVER TO LOWER CASE') AS [DISPLAY VALUE IN LOWER CASE];

SQL SERVER LEN LENGTH FORMULA

SQL SERVER LEN LENGTH FORMULA

-- THEORY

-- SELECT LEN('STRING OR NUMBER FIELD VALUE') AS [ALIAS NAME FOR FIELD VALUE];

-- EXAMPLE

SELECT LEN('TEST001') AS [LENGTH IN NUMBER OF CHARACTERS OF TEXT STRING];

SQL SERVER LEFT FORMULA

SQL SERVER LEFT FORMULA

-- THEORY

-- SELECT LEFT('FIELD VALUE', [NUMBER OF CHARACTERS TO EXTRACT FROM THE LEFT SIDE OF FIELD VALUE]) AS [ALIAS NAME FOR VALUE];

-- EXAMPLE

SELECT LEFT('TEST01', 4) AS [LEFT STRING EXTRACT];

SQL SERVER DATALENGTH FUNCTION

SQL SERVER DATALENGTH FUNCTION

/*DATALENGTH FUNCTION IS SQL SERVER SSMS*/

--ALRIGHT, WE WILL BE LOOKING AT THE DATA LENGTH FUNCTION IN SSMS

--WHICH TELLS YOU HOW MANY CHARACTERS HAVE BEEN USING IN EACH FIELD

--FIRST WE START WITH THE KEYWORD, SELECT

-- FOLLOWED BY THE FUNCTION NAME DATALENGTH(

SELECT DATALENGTH(A.AccountDescription)
FROM DimAccount AS A;

--THE THEORY IS

-- SELECT DATALENGTH(COLUMN NAME)

-- FROM TABLE NAME AS AN ALIAS NAME OF A

-- HIGHLIGHT CODE AND CLICK EXECUTE OR (F5)

-- AS YOU CAN SEE THE AMOUNT OF CHARACTERS USED UP IN EACH FIELD DIFFERS FROM ROW TO ROW

-- THAT IS IT

SQL SERVER CONVERT FUNCTION

SQL SERVER CONVERT FUNCTION

--HI VIEWERS, today we are looking at the CONVERT function for dates

-- the convert function will be used to change what is displayed from a standard date and time value

--here we go

SELECT GETDATE() AS [GET DATE & TIME NOW],CONVERT(date,GETDATE()) AS [GET DATE CONVERSION];

--in the convert function the data type is stated before adding a comma and inserting the date value

--which in this case is GETDATE() which displays the current date and time

--I will click !Execute now or press (F5) to run the code.

--you can see when comparing the results listed below that the conversion has stripped out all

--of the time data.

--that is it.

SQL SERVER FUNCTION CONCAT CONCATENATE

SQL SERVER FUNCTION CONCAT CONCATENATE

--hi viwers, we are going to look at the concatenate function in SQL SERVER 2012 using SSMS

--we are going to append the first name in the customer table to the last name in the customer table

--let us start

SELECT CONCAT(C.FirstName,' ',C.LastName)
FROM DBO.DimCustomer AS C;

--notice that I placed a space in between first name and last name using inverted commas.

--that is it.

SQL SERVER CHARINDEX FORMULA

SQL SERVER CHARINDEX FORMULA

--alright viewers, we will be looking at the CHARINDEX function using SQL SERVER SSMS

--the CHARINDEX function locates the character number position of a specific letter of text within a whole word or sentence

--let us start

--so the function is searching for the letter e within thr text value of test and starting from the location of 1 or

--starting to search from the first letter or value of the text called test

SELECT CHARINDEX('e','test',1);

--the result is 2. so the letter e has been found to be located in the second value of the word test.

--so the displayed answer i 2.

--that is it.

SQL SERVER CHAR FORMULA

SQL SERVER CHAR FORMULA

--alright viewers, we will be looking at the CHAR function using SQL SERVER SSMS

--the CHAR function displays the character symbol for the number you embed in its brackets.

--let us begin

SELECT CHAR(67);

--as you can see the character symbol for the integer of 67 is a capital C.

--let us try some other numbers and see what the come up with.

SELECT CHAR(255);

--you can see that the integer of 255 comes up with a letter like y but in a foreign language.

--let's try one last one

SELECT CHAR(253);

--this one gives a slightly different variation of the letter y.
--that is it.

Monday, 7 November 2016

SQL SERVER ASCII FORMULA

SQL SERVER ASCII FORMULA


--HI ALL, TODAY WE ARE LOOKING AT THE ASCII FUNCTIONS IN SQL SERVER


-- LET'S BEGIN


-- I WILL USE THE PHONE COLUMN FROM THE DIMCUSTOMER TABLE
SELECT Phone, ASCII(PHONE) as [ASCII]
FROM AdventureWorksDW2012.dbo.DimCustomer;


--I HAVE EMBEDDED THE PHONE COLUMN IN THE ASCII FORMULA


--I HAVE ALSE DISPLAYED THE PHONE COLUMN WITHOUT ANY FORMULA SO THAT YOU CAN SEE THE DIFFERENCE


-- NOW I WILL RUN THE QUERY, BY HIGHLIGHTING THE SQL QUERY AND RUNNING IT


-- YOU CAN SEE THE ASCII VALUE IS BASED ON THE FORMAT OF THE PHONE NUMBERS


--THAT IS IT.