European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

How To Split/Separate Numbers And Alphabets From Alpha Numeric String In SQL Server?

clock November 13, 2018 10:11 by author Peter

Today, I am going to explain how you can split/separate numbers and alphabets from an alphanumeric string in SQL server. When you work with any database-related application, either in Web or Windows applications, sometimes based on your requirement you have an alphanumeric string and you only want numbers from that string and want to use those numbers in your entire application as per your need, possibly as a variable, parameter, or a string concatenation.

Implementation
In my case I want to generate auto-increment token number and that token number will generate with a combination of My Invoice Number and Heder Name of Store, and in my Invoice Table Invoice Number like "HSP14569" where "HSP" is Header Name of Store. That can change based on Store selection and "14569" is my Invoice Number.

Actually, what I need is to split my invoice number from "HSP14569" To "14569" and increment with "1," so that will be "14570". Now, I will contact this new number with my header of the store.

So, yesterday I wrote one user-defined function in SQL server, which will return only numeric values from my string.

SQL Server User Defined Function
    CREATE FUNCTION dbo.GetNumericValue 
    (@strAlphaNumeric VARCHAR(256)) 
    RETURNS VARCHAR(256) 
    AS 
    BEGIN 
    DECLARE @intAlpha INT 
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric) 
    BEGIN 
    WHILE @intAlpha > 0 
    BEGIN 
    SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' ) 
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric ) 
    END 
    END 
    RETURN ISNULL(@strAlphaNumeric,0) 
    END 
    GO 


Note
You can modify this user defined function based on your need.

Let's see how you can use this user-defined function. Below, I have included some of the ways to use this function.
 
Sql Server Select Statment
    SELECT dbo.GetNumericValue('') AS 'Empty'; 
    SELECT dbo.GetNumericValue('HSP14569AS79RR5') AS 'Alpha Numeric'; 
    SELECT dbo.GetNumericValue('14569') AS 'Numeric'; 
    SELECT dbo.GetNumericValue('HSP') AS 'String'; 
    SELECT dbo.GetNumericValue(NULL) AS 'NULL'; 


Output

Summary
You can see the result was generated as above. If you have some alternate way to achieve this kind of requirement then please let me know, or if you have some query then please leave your comments.

 

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: All About Primary Key And Its Basics

clock November 7, 2018 08:44 by author Peter

In this series of articles, we will go deep into SQL Server from scratch and will gain knowledge of queries, optimization, and database administration. This is the first article of the series where we will learn about general SQL queries and their functioning. Images have been used wherever necessary so as to make you understand every command properly.

All Queries which I am posting today you can use  directly on your query plan like copy, paste and execute this query.
Each query has a valid column name and similarly I have shown in the form of image for proper understanding and proper usage

Find all Primary key in Give Database in following format,

SELECT i.name AS IndexName, 
    OBJECT_NAME(ic.OBJECT_ID) AS TableName, 
    COL_NAME(ic.OBJECT_ID, ic.column_id) AS ColumnName 
FROM sys.indexes AS i 
INNER JOIN sys.index_columns AS ic 
ON i.OBJECT_ID = ic.OBJECT_ID 
AND i.index_id = ic.index_id 
WHERE i.is_primary_key = 1  


Finding Constrains and Type of Constrain i.e. Primary and foreign key relation in the given database

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint, 
    SCHEMA_NAME(schema_id) AS SchemaName, 
    OBJECT_NAME(parent_object_id) AS TableName, 
    type_desc AS ConstraintType 
FROM sys.objects 
WHERE type_desc IN('FOREIGN_KEY_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT')  


Detailed level relationship and description of primary key and foreign key

SELECT f.name AS ForeignKey, 
    SCHEMA_NAME(f.SCHEMA_ID) SchemaName, 
    OBJECT_NAME(f.parent_object_id) AS TableName, 
    COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
    SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName, 
    OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName, 
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id 
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id 


Use the above snippets as per your requirement.

In most of the cases it's is going to be used in the Database Analysis where Database size and table are large and high in number.

Thus, we learned about the basic queries of SQL. If you have some doubt, or want to add some more information in this article, please feel free to write me in the comments section.

 

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: How To Check If A String Contains A Substring In SQL Server?

clock November 2, 2018 12:14 by author Peter

In this blog, I wil explain how to check a specific word or character in a given statement in SQL Server, using CHARINDEX function or SQL Server and check if the string contains a specific substring with CHARINDEX function.
 
Alternative to CHARINDEX() is using LIKE predicate.
 
Method 1 - Using CHARINDEX() function

CHARINDEX()
This function is used to search for a specific word or a substring in an overall string and returns its starting position of match. In case no word is found, then it will return 0 (zero).
 
Let us understand this with examples.

Syntax
    CHARINDEX ( SearchString,WholeString[ , startlocation ] )

Example
    Declare @mainString nvarchar(100)='Kenneth James    ' 
    ---Check here @mainString contains Kenneth or not, if it contains then retrun greater than 0 then print Find otherwise Not Find 
    if CHARINDEX('Kenneth',@mainString) > 0  
    begin 
       select 'Find' As Result 
    end 
    else 
        select 'Not Find' As Result 


Output

CHARINDEX
 
Method 2 - Using LIKE Predicate
    DECLARE @WholeString VARCHAR(50) 
    DECLARE  @ExpressionToFind VARCHAR(50) 
    SET @WholeString = 'Kenneth James' 
    SET @ExpressionToFind = 'James' 
      
    IF @WholeString LIKE '%' + @ExpressionToFind + '%' 
        PRINT 'Yes it is find' 
    ELSE 
        PRINT 'It doesn''t find' 

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in