sql query needed

Question

Post reply

sql query needed

freecoder

SSC Eights!

Points: 951
More actions
December 30, 2011 at 4:08 am

#248914

basically i need a query to fetch serial number and letter from the column which has two type of data
12345 0047225P1
00011 0055500ABC
The first data should have serial number = 004722 and letter = P
The second data should have serial number = 0055500AB and letter = C
Thanks

Viewing 15 posts - 1 through 15 (of 18 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You're not telling much about your data.

Can you post the table script, some sample data and the expected results based on the sample data?

Also, what makes P the letter in the first row and C in the last row?

Is there a logic that can be implemented (eg. last non numeric char in the string?)

-- Gianluca Sartori

freecoder SSC Eights! Points: 951 More actions · Answer 2

create table abc

(

SerialNumber varchar (50)

)

insert into abc (SerialNumber)values ('12345 0047225P1')

insert into abc (SerialNumber)values ('00011 0055500ABC')

I am expecting a table with once column name account number and the result like

Account Number

P 0047225

C 0055500

I hope this answers your questiong

LutzM SSC Guru Points: 107049 More actions · Answer 3

Based on what you provided so far, this could be one solution:

SELECT '004722' AS [serial number] , 'P' AS letter

UNION ALL

SELECT '0055500AB' AS [serial number] , 'C' AS letter

If that's not what you're looking for, please provide more details as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

mario.balatellii (12/30/2011)
create table abc ( SerialNumber varchar (50) ) insert into abc (SerialNumber)values ('12345 0047225P1') insert into abc (SerialNumber)values ('00011 0055500ABC')
I am expecting a table with once column name account number and the result like
Account Number
P 0047225
C 0055500
I hope this answers your questiong

What makes P the letter for the first row and C for the last row?

-- Gianluca Sartori

LutzM SSC Guru Points: 107049 More actions · Answer 5

Didn't see your latest reply, I'm sorry...

What is the logic to get "P" based on "0047225P1"? Shouldn't it be "1"?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

freecoder SSC Eights! Points: 951 More actions · Answer 6

Thanks for your replies

i am looking for something like this

DECLARE @STR VARCHAR(100)

SET @STR = '12345 0047225P1'

SELECT SUBSTRING(@Str,CHARINDEX(' ',@Str)+8,1) + ' '+ SUBSTRING(@Str,CHARINDEX(' ',@Str)+1,7) AccountNo

I thot the data in the column serious number will always be like 12345 0047225P1

but there is one more patern like 00011 0055500ABC. So if i knew the logic my self i would have done it .

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

mario.balatellii (12/30/2011)
So if i knew the logic my self i would have done it .

I guess you will have to ask that to somebody who knows the business logic.

I don't think we can help here.

-- Gianluca Sartori

freecoder SSC Eights! Points: 951 More actions · Answer 8

Ok i analysed the data and got a clue

create table abc

(

SerialNumber varchar (50)

)

insert into abc (SerialNumber)values ('12345 0047225P1')

insert into abc (SerialNumber)values ('00011 0055500ABC2')

So basically there is the letter to choose should precede a number

like in first data P is before 1

and second data C is before 2

any help?

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 9

However it's late to point it but there are two threads running for same issue in SSC. Both have more than 5 replies. 😀

This is the early thread.

http://www.sqlservercentral.com/Forums/Topic1227680-392-1.aspx

LutzM SSC Guru Points: 107049 More actions · Answer 10

Thanx, Dev.

At least I didn't waste too much time on it...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 11

DECLARE @abc TABLE

(

SerialNumber varchar (50)

)

insert into @abc (SerialNumber)values ('12345 0047225P1')

insert into @abc (SerialNumber)values ('00011 0055500ABC2')

SELECT

SUBSTRING(SerialNumber, PATINDEX('% %', SerialNumber) + 1, PATINDEX('%[^0-9][0-9]', SerialNumber) - PATINDEX('% %', SerialNumber) - 1) AS SerialNumber,

SUBSTRING(SerialNumber, PATINDEX('%[^0-9][0-9]', SerialNumber) , 1) AS Letter

FROM @abc

-- Gianluca Sartori

freecoder SSC Eights! Points: 951 More actions · Answer 12

freecoder

SSC Eights!

Points: 951

December 30, 2011 at 5:55 am

#1426908

thank you very much Sir!

freecoder SSC Eights! Points: 951 More actions · Answer 13

Good Morning...

DECLARE @abc TABLE

(

SerialNumber varchar (50)

)

insert into @abc (SerialNumber)values ('00132 0045749WSE1')

insert into @abc (SerialNumber)values ('00181 0102670N1')

I want to get the account number and the First Alphabet after the account number

Something like

Account Number Account Letter

0045749 W

0102670 N

Regards

spaghettidba SSC Guru Points: 105732 More actions · Answer 14

SELECT

SUBSTRING(SerialNumber, PATINDEX('% %', SerialNumber) + 1, PATINDEX('%[^0-9 ]%', SerialNumber) - PATINDEX('% %', SerialNumber) -1) AS SerialNumber,

SUBSTRING(SerialNumber, PATINDEX('%[^0-9 ]%', SerialNumber) , 1) AS Letter

FROM @abc

-- Gianluca Sartori