February 19, 2014 at 12:26 pm
Hello, I am trying to add 0's to a field directly after an alpha character.
Table name is client and field name is client_num
For the client codes that are currently alpha numeric the zeros will be inserted.
client_num P1234 becomes P001234
client_num C1234 becomes C001234
Insert the 00 after the first letter
I am not sure how to go about this.
February 19, 2014 at 12:28 pm
You could do it easily with the STUFF function.
Would you need help on how to do it?
February 19, 2014 at 12:29 pm
Yes, please. I am not familiar with that function.
February 19, 2014 at 12:31 pm
There is also the replace option such as follows.
WITH client AS (SELECT 'P1234' AS client_num
UNION
SELECT 'C1234' AS client_num
)
SELECT REPLACE(client_num,LEFT(client_num,1),LEFT(client_num,1) + '00') AS client_num
FROM client;
Stuff would probably be a little simpler.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 19, 2014 at 12:31 pm
That's why I included a link to BOL. You should check it to know exactly what's happening. Be sure to ask any questions you have after reading it.
Here's an example for your problem.
STUFF( client_num, 2, 0, '00')
February 19, 2014 at 12:31 pm
The string in those fields can start with any Letter not always C or P
February 19, 2014 at 12:34 pm
tschuler-738392 (2/19/2014)
The string in those fields can start with any Letter not always C or P
None of the solutions given depend on the field starting with C or P. However, it's assumed that there will always be a single letter at the beginning. If that's not the case, be sure to tell us.
February 19, 2014 at 12:34 pm
tschuler-738392 (2/19/2014)
The string in those fields can start with any Letter not always C or P
The CTE is provided only as a means to ingest the data you provided. You would replace the call to the CTE with your actual table. The code will replace the first character with the three character string in my example (as you specified).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 19, 2014 at 12:37 pm
thanks. After looking at your query it made sense. I also just read the information on that Function. Thank you.
February 19, 2014 at 12:38 pm
Here is the code example for the stuff function using a CTE and the provided sample data as well.
WITH client AS (SELECT 'P1234' AS client_num
UNION
SELECT 'C1234' AS client_num
)
SELECT STUFF( client_num, 2, 0, '00')
FROM client;
Compared to the Replace option, with minimal records, both come out even in time and cost. But you should probably do some testing on your end.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 19, 2014 at 12:39 pm
tschuler-738392 (2/19/2014)
thanks. After looking at your query it made sense. I also just read the information on that Function. Thank you.
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply