CODEWITHSUNDEEP
sqlsql-serversql-server-2008parsing
slevin37
slevin37

Reputation: 33

Parse from string specific values in SQL Server

I have a column with a very long string, and I need to be able to parse out specific values from the string (i.e. values 67-70 for the state name). Below is the (long) string I am working with. I am assuming I can use the Parsename function but I'm unsure of the syntax. 

H0100343107000100000000000151750A P+++++++++++++++++1016   STANLEY     YOUNG                                        17 SPRAYPOINT DRIVE           POINT COOK             FO000006140949525A N                              WEB SITE  S                                    3030      00010VICTORIA                      61409495255

Upvotes: 0

Views: 3178

Answers (2)

Shukri Gashi
Shukri Gashi

Reputation: 535

If you want to extract something from string you have two solutions within t-sql (no CLR):

  1. By position
  2. Splitting string using delimiter

1 - String functions which can be used by position are: SUBSTRING, LEFT, RIGHT

2 - There is no build in function for splitting string in t-sql based on delimiter. You can write your function to split it. Below is some splitting function:

CREATE FUNCTION [dbo].[Split]
(
    @Text VARCHAR(MAX),
    @Delimiter VARCHAR(100),
    @Index INT
)
RETURNS VARCHAR(MAX)
AS BEGIN
    DECLARE @A TABLE (ID INT IDENTITY, V VARCHAR(MAX));
    DECLARE @R VARCHAR(MAX);
    WITH CTE AS
    (
    SELECT 0 A, 1 B
    UNION ALL
    SELECT B, CONVERT(INT,CHARINDEX(@Delimiter, @Text, B) + LEN(@Delimiter))
    FROM CTE
    WHERE B > A
    )
    INSERT @A(V)
    SELECT SUBSTRING(@Text,A,CASE WHEN B > LEN(@Delimiter) THEN B-A-LEN(@Delimiter) ELSE LEN(@Text) - A + 1 END) VALUE      
    FROM CTE WHERE A >0

    SELECT      @R
    =           V
    FROM        @A
    WHERE       ID = @Index + 1
    RETURN      @R
END

Upvotes: 0

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

You should use substring

SELECT SUBSTRING('w3resource',4,3);

will out put eso 4,3 means start from 4th position till next 3 characters

so in your case it will be

SELECT SUBSTRING(column_name,67,4);

This is all about MYSQL but MS SQL has the same function 

SUBSTRING( string, start_position, length )

Please check this link http://social.technet.microsoft.com/wiki/contents/articles/17948.t-sql-right-left-substring-and-charindex-functions.aspx

Upvotes: 2

Related Questions