Split and Set values for the fields in Stored Procedure

Question

Split and Set values for the fields in Stored Procedure

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
September 6, 2016 at 2:32 pm

#321276

Hello,
I am trying to get loc 1 and loc2 columns from a table.
We have another 2 columns descr1 and descr2 in the same table where it has data like under descr1 'USA' and under description2 'NY-Y0001'
so now in loc1 and loc2 I want the data to be like loc1 as 'NY' and loc2 as 'Y0001'.
select ISNULL(Loc1,' ') AS Loc1Id,
ISNULL(Loc2,' ') AS Loc2Id from table.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

mcfarlandparkway (9/6/2016)
Hello,
I am trying to get loc 1 and loc2 columns from a table.
We have another 2 columns descr1 and descr2 in the same table where it has data like under descr1 'USA' and under description2 'NY-Y0001'
so now in loc1 and loc2 I want the data to be like loc1 as 'NY' and loc2 as 'Y0001'.
select ISNULL(Loc1,' ') AS Loc1Id,
ISNULL(Loc2,' ') AS Loc2Id from table.

Look at what you posted and ask yourself if you could answer this based on nothing other than the information provided. It is totally unclear what you are trying to do here. However, the solution to your issue is quite simple once you post details. Please see the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 2

mcfarlandparkway (9/6/2016)
Hello,
I am trying to get loc 1 and loc2 columns from a table.
We have another 2 columns descr1 and descr2 in the same table where it has data like under descr1 'USA' and under description2 'NY-Y0001'
so now in loc1 and loc2 I want the data to be like loc1 as 'NY' and loc2 as 'Y0001'.
select ISNULL(Loc1,' ') AS Loc1Id,
ISNULL(Loc2,' ') AS Loc2Id from table.

--===== Just a demo with a single variable. Modify to use a table

-- Also assumes a fixed format here. Let us know if it's not.

-- As a sidebar, I suggest you get familiar with the functions in T-SQL.

-- Functions are where a whole lot of the power is.

-- Each formula could be used in a Persisted Computed Column so it could be indexed

-- and would happen auto-magically on insert into the table.

DECLARE @SomeColumn CHAR(8) = 'NY-Y0001'

;

SELECT Loc1 = LEFT(@SomeColumn,2)

,Loc2 = RIGHT(@SomeColumn,5)

WHERE @SomeColumn LIKE '[A-Za-z][A-Za-z]-[A-Za-z][0-9][0-9][0-9][0-9]'

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)