select pattern from the column name

Question

Post reply

select pattern from the column name

gnaneshs

SSC Enthusiast

Points: 196
More actions
January 5, 2011 at 3:00 pm

#243224

i want to read '_1_ ' or '_2_' from the "file" column in the table as shown
###############################################################
file input output
--------- ------- ----------
MONTH_20101006_1_out.txt 75375
MONTH_20101001_1_IN.txt 35935
MONTH_20101003_2_IN.txt 84558455
MONTH_20101003_1_IN.txt 4691
MONTH_20101001_1_IN.txt 55345
MONTH_20101006_1_IN.txt 68668
MONTH_20101005_1_IN.txt 591829
MONTH_20101001_2_IN.TXT 47647
MONTH_20101001_1_IN.txt 55355
#################################################################
i wrote
select
case
when (file like '%_2_%' ) then 2
when (file like '%_1_%' ) then 1
else null end as file_num
from month_in ;
but its not working.....
can any one solve it

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

Check out the article here. The _ character is what is causing you problems. This should explain how to deal with it.

_______________________________________________________________

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/

gnaneshs SSC Enthusiast Points: 196 More actions · Answer 2

gnaneshs

SSC Enthusiast

Points: 196

January 5, 2011 at 3:15 pm

#1269786

can u gimme the solution plzz

JackIntheBox SSCrazy Points: 2022 More actions · Answer 3

If you had followed sean's advice you would have reached to a solution.

Anyways here it is; try to read what others are indicating , it might help

select

case

when (file like '%[_]2[_]%' ) then 2

when (file like '%[_]1[_]%' ) then 1

else null end as file_num

from month_in ;

gnaneshs SSC Enthusiast Points: 196 More actions · Answer 4

its not working bro

can u plzz see it

the output having only nulls

Lowell SSC Guru Points: 323518 More actions · Answer 5

it's working for me, but you had a lot of reserved words for your column names, so i quoted them, and see it's working that way.....:

With MyQuickTable AS (

SELECT 'MONTH_20101006_1_out.txt' AS [file],'75' AS [input],'375' AS [output],'' AS [blank]UNION ALL

SELECT 'MONTH_20101001_1_IN.txt','35','935','' UNION ALL

SELECT 'MONTH_20101003_2_IN.txt','8455','8455','' UNION ALL

SELECT 'MONTH_20101003_1_IN.txt','46','91','' UNION ALL

SELECT 'MONTH_20101001_1_IN.txt','55','345','' UNION ALL

SELECT 'MONTH_20101006_1_IN.txt','68','668','' UNION ALL

SELECT 'MONTH_20101005_1_IN.txt','59','1829','' UNION ALL

SELECT 'MONTH_20101001_2_IN.TXT','47','647','' UNION ALL

SELECT 'MONTH_20101001_1_IN.txt','55','355','')

select

case

when ([file] like '%[_]2[_]%' ) then 2

when ([file] like '%[_]1[_]%' ) then 1

else null end as file_num

from MyQuickTable ;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

Just simply giving you the solution is not the best approach on two levels. First you aren't learning anything about how to deal with this in the future. Secondly, I am then providing consulting at no cost. At any rate, I hope you were able to get the solution and fix your issue.

_______________________________________________________________

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/