Parse from string specific values in SQL Server

Question

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

Tip of today: Store your data in separate columns instead of that horrible string. — jarlh
– jarlh, Commented Jan 25, 2016 at 12:54
Unfortunately its not my call. It part of an assignment. Trust me, I would never store data in a string like this! — slevin37
– slevin37, Commented Jan 25, 2016 at 13:00

Danyal Sandeelo · Accepted Answer · 2016-01-25 12:58:04Z

2

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

edited Jan 25, 2016 at 12:58

answered Jan 25, 2016 at 12:53

Danyal Sandeelo

12.4k10 gold badges57 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

I think the length is 4, not 3 -- 67, 68, 69. 70.

Shukri Gashi · Accepted Answer · 2016-01-25 13:08:48Z

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

By position
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

Collectives™ on Stack Overflow

Parse from string specific values in SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related