Few days back, my friends had asked a question “I want to get only integer part in string. for example, string contain ‘asb645pqr231means’ i want only integer part like 64523. how can i get?“. I have mentioned two method to do it.

Method 1:
SET NOCOUNT ON
DECLARE @loop INT
DECLARE @str VARCHAR (8000)
SELECT @str = ‘asb645pqr231’
SET @loop = 0
WHILE @loop < 26
BEGIN
SET @str = REPLACE (@str, CHAR (65 + @loop), ”)
SET @loop = @loop + 1
END
SELECT @str

Method 2:
SET NOCOUNT ON
DECLARE @KeepValues VARCHAR (50)
DECLARE @Str VARCHAR (500)
SET @Str= ‘asb645pqr231’
SET @KeepValues = ‘%[^a-z]%’ — if you want only characters in the output
SET @KeepValues = ‘%[^0-9]%’ — If you want only integers in the output
WHILE PATINDEX (@KeepValues, @Str) > 0
SET @Str = STUFF (@Str, PATINDEX(@KeepValues, @Str), 1, ”)
select @Str

There may be more ways do it.

Share this:

In MS SQL Server, we can use a CASE statement to implement IF…THEN. Here is the example of the CASE statement.

If your logic is as follows:

IF 1 > -1
THEN
‘TRUE’
ELSE
‘FALSE’

You can use CASE statement as follows:

— SQL Server 2008 and earlier version solution
SELECT CASE WHEN -1 < 1 THEN ‘TRUE’ ELSE ‘FALSE’ END AS Result
GO
A New function “IIF” has introduced in MS SQL 2012. This function is shorthand way for writing CASE statement. These functions take three arguments. If the first argument is true, it will return the second argument as result or it will return the third argument as result.

— SQL Server 2012 solution

Example 1: IIF Usage
SELECT IIF ( -1 < 1, ‘TRUE’, ‘FALSE’ ) AS Result;
GO

Example 3: IIF with NULL
DECLARE @Variable INT=NULL
SELECT IIF ( @Variable IS NULL, ‘Value is Null’, ‘Value is NOT NULL’ ) AS Result;
GO

Example 2: Nested IIF
SELECT IIF ( 1 > -1, IIF ( 1 = 1, ‘Inner True’, ‘Inner False’ ), ‘FALSE’ ) AS Result;

In above example, we can see how IIF is used instead of CASE statement. IIF can be used the same way as CASE statement in SELECT statement.

Share this:

I have tried to explain the usage of simple WHILE loop in first example. BREAK keyword will exit the stop the while loop and control is moved to next statement after the while loop. CONTINUE keyword skips all the statement after its execution and control is sent to first statement of while loop. Run following examples in Query Editor and see the result. This is very easy to understand example.

1. Example of WHILE Loop

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
GO

ResultSet:
1
2
3
4
5

2. Example of WHILE Loop with BREAK keyword

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
IF @intFlag = 4
BREAK;
END
GO

ResultSet:
1
2
3

3. Example of WHILE Loop with CONTINUE and BREAK keywords

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
CONTINUE;
IF @intFlag = 4 — This will never executed
BREAK;
END
GO

ResultSet:
1
2
3
4
5

Share this: